How to apply aggregations and filters on aggregated data

[adityaPsl (Aditya)]

Hello Team,

I have a use case where i need to apply filters and metrics on aggregated data.

for example i have below employee promotion data.

+----+-------+------+-------------+---------------------+
| id | empid | name | designation | promdate            |
+----+-------+------+-------------+---------------------+
|  1 | e1    | aa   | d1          | 2019-03-06 20:28:45 |
|  2 | e2    | bb   | d2          | 2019-03-06 20:29:18 |
|  3 | e1    | aa   | d2          | 2019-03-06 20:29:28 |
|  4 | e2    | bb   | d3          | 2019-03-06 20:29:41 |
|  5 | e3    | cc   | d4          | 2019-03-06 20:30:22 |
|  6 | e3    | cc   | d5          | 2019-03-06 20:30:36 |
+----+-------+------+-------------+---------------------+

aggregated data based on empid

+----+-------+------+-------------+---------------------+
| id | empid | name | designation | promdate            |
+----+-------+------+-------------+---------------------+
|  6 | e3    | cc   | d5          | 2019-03-06 20:30:36 |
|  4 | e2    | bb   | d3          | 2019-03-06 20:29:41 |
|  3 | e1    | aa   | d2          | 2019-03-06 20:29:28 |
+----+-------+------+-------------+---------------------+

and i want to apply count and filter as employees in designation d5 should not be part of the list and count the employees not having designation as d5.

using Kibana data table visualization i was able to get the latest unique value of employee but not able to apply filters and count aggregations on the data which is aggregated by empid.

any pointers to resolve this issue is really appreciated.
Thank you for your help and support.

Thank you,
Aditya

[ppisljar (Peter Pisljar)]

could you also show how your expected output would look like ?

if you want to filter our all employes with designation d5 from data just add a filter to the filter bar:

• click the add filter button in the filter bar
• select field designation
• select 'not one of'
• enter 'd5'

if you save your visualizations the filters you added will be saved with it.

[adityaPsl (Aditya)]

Thank you ppisljar for your reply.

the final output should look like this
    ----+-------+------+-------------+---------------------+-------+------+------
     id | empid | name | designation | promdate                    
          4 | e2    | bb   | d3          | 2019-03-06 20:29:41 
          3 | e1    | aa   | d2          | 2019-03-06 20:29:28 

----+-------+------+-------------+---------------------+-------+----

basically it should show the latest promotion received by the employee but not d5.

I tried the solution you mentioned , but when i try to apply filter it gets applied to the aggregated result and employee 'e3' is shown with its 'd4'promotion but that is not intended.

as a workaround i am creating a one more index with empid as primary key , so there will be only one and latest record available in the new index and applying aggregations like count and filters on that data. However i am not sure if this is the right and recommended way to handle the situation.

any thoughts or suggestions are really appreciated.

Thank you very much for your help and support.

Thank you,
Aditya

[adityaPsl (Aditya)]

Hello Team,

Just wanted to check if there is an option to get unique latest record for employee and create saved search and create data table or aggregation on that saved search.

Thank you for your help and support.

Thank you,
Aditya

[adityaPsl (Aditya)]

Hello Team,

Could you please help me with pointers for applying metrics like count , sum or filters on aggregated data based on unique field.

for above use case a query similar to

select emp.empid,emp.name,emp.promdate,emp.designation from emp inner join (select empid,name,designation,max(promdate) as latest from emp group by empid) r on emp.promdate = r.latest and emp.e
mpid = r.empid  where emp.designation !='d5' order by promdate desc;

and output would be latest promotion details with d5 excluded

+-------+------+---------------------+-------------+
| empid | name | promdate            | designation |
+-------+------+---------------------+-------------+
| e2    | bb   | 2019-03-06 20:29:41 | d3          |
| e1    | aa   | 2019-03-06 20:29:28 | d2          |
+-------+------+---------------------+-------------+

any pointers to resolve the issue are really appreciated.

Thank you,
Aditya