Group by multiple attribute and get count per day

[anilbabuk]

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

[a_m_s]

@anilbabuk 

| eval TT=File."-".User
| fields - File User
| chart count over TT by Date
| eval File=mvindex(split(TT,"-"),0)
| eval User=mvindex(split(TT,"-"),1)
| fields - TT
| table File User *

[tscroggins]

@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.

[tscroggins]

@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