Count records with or without Summarize tool

[Sneha_Lonkar]

Hi All,

I am working on the below data. I want to get the count of Employee ID that is falling under same date and under same hour bucket. For example: A123 is coming 2 times on a particular date 06-05-2020 under the single bucket 14:00-15:00.

How can I get the desired counts by using Summarize tool or by using any other tool?

I tried using Summarize tool by grouping Date, Employee Id and Hour bucket and then taking count of Employee ID. But i am not getting the correct output. Kindly help.

Employee ID	Date	Time	Hour Bucket	Count
A123	06-05-2020	14:01:23	14:00-15:00	2
A123	06-05-2020	14:43:14	14:00-15:00	2
B123	07-05-2020	13:07:56	13:00-14:00	1
B123	08-05-2020	14:04:34	14:00-15:00	1
B123	06-05-2020	10:00:06	10:00-11:00	1
C123	06-05-2020	11:23:00	11:00-12:00	2
A123	07-05-2020	08:43:32	8:00-9:00	1
A123	06-05-2020	08:12:01	8:00-9:00	1
C123	06-05-2020	11:56:00	11:00-12:00	2

Thanks in advance.

[joshbennett]

Hi @Sneha_Lonkar 

You were very close - the reason your Summarize did not work was simply because you included the Employee ID field in your 'Group By' fields.

Since this is the field you are trying to count the occurrences of for specific date and hour bucket combinations, you would want to only 'Group By' the Date and Hour Bucket fields, while specifying 'Count' for the Employee ID (instead of 'Group By').

Let me know if this works for you.

[grazitti_sapna]

Hi @Sneha_Lonkar,

Here is my take on your problem. I hope it solves your issue:

 

If it solves your problem then please mark it as the solution.

[Sneha_Lonkar]

Hi DextersLaboratory

8 - Asteroid

I tried your method but for the same date it is giving me different counts. Say for hour bucket 14:00-15:00, the count should be 2 across the date 06-05-2020 for Employee A123. But in my case the count is not constant. I have 40k+ records with around 30 employee IDs.

[joshbennett]

@Sneha_Lonkar 

Maybe I am misunderstanding you, but how are you saying the results are not as expected?

In my example workflow, the counts were based off of the sample dataset that you provided, which is only a total of 9 records. Thus, you would not / should not expect to see the same count values reflected in the same workflow that instead has 40K records. 

If you just mean that you want to see the Employee ID along with the count results in my example, all you'd have to do is Join the results of the Summarize back to the data that was fed into the Summarize, as in @grazitti_sapna's example. 

Let me know if I am missing your question. 

[kumaranvr1984 · Accepted solution]

Hi Sneha,

You have to drop the Time field from summarize tool. while you add time field this will split the output that is why you are getting duplicate counts, Just group Employee id, Date and hour bucket then you will get the expected output.

Hope this helps. 

[tonysavio]

Hey Guys,

I know this is a solved issue. But I have a question related to this.

As you can see from the image, I am trying to count the days between two days. But I am trying to exclude Saturdays and Sundays as this is a count for business days. Thats why the weekday field is generated.

The issue I am facing is that, wherever the gap between two days is 0 (eg. same day start and end), I am getting the output as 1 from the count instead of 0. I know that in SQL, count will not print values that are 0, but is it applicable here? Is there any other tool I can use in Alteryx instead of Count?