topic Re: Group by multiple attribute and get count per day in Dashboards & Visualizations

Group by multiple attribute and get count per day

anilbabuk — Fri, 14 Jan 2022 15:12:01 GMT

I have data like below

Date	File	User
1/10/2022	F1	U1
1/10/2022	F1	U2
1/10/2022	F2	U1
1/11/2022	F3	U3
1/11/2022	F1	U1
1/11/2022	F2	U2
1/11/2022	F3	U3
1/11/2022	F1	U1
1/12/2022	F2	U1
1/12/2022	F3	U2
1/12/2022	F1	U1
1/12/2022	F2	U1

I would like to Group the data with File & user and needs to get count for each per day as below. Please let me know how can I get like this?

File	User	1/10/2022	1/11/2022	1/12/2022
F1	U1	1	2	1
F2	U1	1		2
F1	U2	1
F2	U2		1
F3	U2			1
F3	U3		2

Re: Group by multiple attribute and get count per day

tscroggins — Sun, 16 Jan 2022 18:43:31 GMT

@anilbabuk

If you have a data model, you can use the pivot command:

| pivot My_Data_Model My_Dataset count(My_Dataset) SPLITROW File SPLITROW User SPLITCOL Date | sort User File | foreach */*/* [ eval <<FIELD>>=nullif('<<FIELD>>', 0) ]

If you do not have a data model, you'll need to manipulate the output of chart, stats, etc. to split columns by multiple row fields:

```base search here``` | stats count by File User Date | eval {Date}=count | stats max(*/*/*) as */*/* by File User | sort User File

Re: Group by multiple attribute and get count per day

a_m_s — Wed, 19 Jan 2022 14:59:29 GMT

Re: Group by multiple attribute and get count per day

tscroggins — Wed, 19 Jan 2022 23:59:37 GMT

@a_m_s

This works as long as File and User never contain a hyphen. There's no 100% safe method of concatenating and later separating strings for grouping without storing and comparing the original values, but if you understand your dictionary, you can choose values that reduce the probability of a collision. The values provided in the original question may just be examples.