Count records with or without Summarize tool

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.

Developer Tools

Accepted answers

kumaranvr1984

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.

All comments

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.

Count_join.yxzp

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.

kumaranvr1984

Hi Sneha,

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703