Busiest Hour per Day based on Count

Question
1

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
2

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

image
image956×219 59.5 KB

busiest.transform (4.1 KB)

3

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

image
image1135×497 46.3 KB

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

4

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.

5

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

image
image1653×443 47.9 KB

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
6

Hi,

Try this alternate transform

image
image500×647 72.8 KB

BusiestHourPerDay.transform (3.1 KB)

1 Like
7

Here is the .csv file for BusiestHourPerDay.transform:

BusiestHourPerDay.csv (830 Bytes)

You can also do it in a few less transfroms using the new ranking feature in the latest snapshot.

8

Hi,

Here it is, with the new Rank feature in Sort Transform

image
image500×589 89.4 KB

I forgot to add the data file and thanks to Andy for providing it for the previous post.

Data file
BusiestHourPerDay.csv (830 Bytes)

Transform file
BusiestHourPerDaywithRank.transform (2.6 KB)

For this transform, you have to have 1.34.1 snapshot version.

1 Like