Busiest Hour per Day based on Count

How do you get the Busiest Hour per Day using Count values, but there is an additional column named Country & not all countries have data for all dates?

Here is a sample of the data:

Date	Time	Country	Count
01 Apr	11:00	UK	1
01 Apr	16:00	UK	4
01 Apr	13:00	UK	23
01 Apr	17:00	UK	43
01 Apr	10:00	UK	53
01 Apr	22:00	UK	60
01 Apr	4:00	UK	61
01 Apr	7:00	UK	75
01 Apr	8:00	UK	84
01 Apr	19:00	UK	99
01 Apr	1:00	UK	134
01 Apr	21:00	UK	189
01 Apr	14:00	UK	230
01 Apr	5:00	UK	279
01 Apr	12:00	UK	381
01 Apr	3:00	UK	423
01 Apr	15:00	UK	486
01 Apr	0:00	UK	489
01 Apr	2:00	UK	544
01 Apr	6:00	UK	566
01 Apr	20:00	UK	679
01 Apr	18:00	UK	693
01 Apr	23:00	UK	832
01 Apr	9:00	UK	1045
02 Apr	1:00	Portugal	6
02 Apr	20:00	Portugal	10
02 Apr	19:00	Portugal	19
02 Apr	9:00	Portugal	27
02 Apr	7:00	Portugal	28
02 Apr	13:00	Portugal	31
02 Apr	22:00	Portugal	44
02 Apr	18:00	Portugal	55
02 Apr	17:00	Portugal	75
02 Apr	2:00	Portugal	97
02 Apr	15:00	Portugal	159
02 Apr	6:00	Portugal	216
02 Apr	12:00	Portugal	233
02 Apr	23:00	Portugal	236

Here is one way. There might be a more elegant one.

busiest.transform (4.1 KB)

Hi Andy, it seems like when using a larger database, it does not work anymore, as the Join is picking up duplicates.

Have tried to use a dedupe between the lower Concat Cols (where the duplicates comes from) & the Join, the duplicates disappears, but it does not give unique max hour per day anymore

Your example helped me understand how Join works so I build one that works, it just does not contain the country code anymore, but it works for the busiest hour per day

If there are duplicates in the input to the join, you may get duplicates in the output. It might be appropriate to remove them using Unique or Dedupe.

Hi Andy,
This works 100% even if there is 4+ extra columns :smile:

I used a 5 million row CSV file as well in another database app & did the same and it matched 100% , thank you very much :smile:

1 Like