How can I count a field grouped by fields from 2 different Sources

[taichii]

I am trying to count the employees per location during a particular shift and date. I'm pretty new to SPLUNK and I am approaching the searches like a SQL query. I can display them if I am not going to count the employees using this.

source=something1OR source=something2 host=somethinghost index=somethingindex sourcetype=csv
| stats values(Location_id) as LocationID, values(Employee_ID) as EmpID, values(Shift_id) as Shift by Employee_ID,Date
| table Date,LocationID,EmpID, Shift

I tried replacing values in Employee_ID to Count and playing around with the "by" but the count ends of as 2 for all instead of 1.

Shifts

Shift_id	Employee_ID	Date
1	994163	8/15/2020
2	123456	8/15/2020
3	654321	8/15/2020
1	994163	8/16/2020
2	123456	8/16/2020
3	654321	8/16/2020
1	991234	8/16/2020

Locations

Location_id	Employee_ID	Date
L01	994163	8/15/2020
L02	123456	8/15/2020
L03	654321	8/15/2020
L01	994163	8/16/2020
L02	123456	8/16/2020
L03	654321	8/16/2020
L07	991234	8/16/2020

desired output

date	LocationID	count	Shift
8/15/2020	L02	1	2
8/15/2020	L03	1	3
8/15/2020	L01	1	1
8/16/2020	L02	1	2
8/16/2020	L03	1	3
8/16/2020	L07	1	1
8/16/2020	L01	1	1

what i am getting

date	LocationID	count	Shift
8/15/2020	L02	2	2
8/15/2020	L03	2	3
8/15/2020	L01	2	1
8/16/2020	L02	2	2
8/16/2020	L03	2	3
8/16/2020	L07	2	1
8/16/2020	L01	2	1

[to4kawa]

sample:

| makeresults
| eval _raw="Shift_id	Employee_ID	Date
1	994163	8/15/2020
2	123456	8/15/2020
3	654321	8/15/2020
1	994163	8/16/2020
2	123456	8/16/2020
3	654321	8/16/2020
1	991234	8/16/2020"
| multikv forceheader=1
| append [| makeresults
| eval _raw="Location_id	Employee_ID	Date
L01	994163	8/15/2020
L02	123456	8/15/2020
L03	654321	8/15/2020
L01	994163	8/16/2020
L02	123456	8/16/2020
L03	654321	8/16/2020
L07	991234	8/16/2020"
|multikv forceheader=1 ]
| fields - _* linecount
| selfjoin Date Employee_ID
| eventstats count by Date Employee_ID

try selfjoin and eventstats