Count of Occurrences from the transaction DateTime + 1 Hour

[ishanbansal1204]

Transaction Time      A     B    C      Expected Output
3/17/2020 14:42      India  1   10        2
3/17/2020 15:09      India  1   10        0
3/17/2020 15:48      India  1   10        4
3/17/2020 15:59      India  1   10        0
3/17/2020 16:13      India  1   10        0
3/17/2020 16:36      India  1   10        0
3/17/2020 17:02      India  1   10        1
3/17/2020 18:42      India  1   10        5
3/17/2020 18:55      India  1   10        0
3/17/2020 19:05      India  1   10        0
3/17/2020 19:24      India  1   10        0
3/17/2020 19:30      India  1   10        0
3/17/2020 20:01      India  1   10        1
3/17/2020 21:24      India  1   10        3
3/17/2020 21:26      India  1   10        0
3/17/2020 21:48      India  1   10        0

The Dataset is based on the transaction Datetime with some of the dimensions associated to it.

The first transaction occurred on: 3/17/2020 14:42 , I want to know how many such transactions are available between the first transaction date and next 1 hour interval. So In the data we can see the first and second record satisfies this criteria so the last column "Expected Output" as 2 in the first row and 0 in the 2nd row.

again the counter will start from the third records : 3/17/2020 15:48 so for this record we can see there are total of 4 such transactions which falls between and current datetime + 1 hour interval so the 3 row is set as 4 and the row 4,5,6 is set as 0 and so on.

[Saravanan_Desingh]

Try this,

tab1:
LOAD *, If(Time(Peek(BaseTm)+1/24)>=Time(TmTran),Peek(BaseTm),TmTran) As BaseTm;
LOAD *, Date(Floor(Timestamp#([Transaction Time],'MM/DD/YYYY hh:mm'))) As DtTran, Time(Frac(Timestamp#([Transaction Time],'MM/DD/YYYY hh:mm'))) As TmTran;
LOAD RecNo() As ID,* INLINE [
    Transaction Time,       A,      B,     C,       Expected Output
    3/17/2020 14:42,       India, 1, 10, 2
    3/17/2020 15:09,       India, 1, 10, 0
    3/17/2020 15:48,       India, 1, 10, 4
    3/17/2020 15:59,       India, 1, 10, 0
    3/17/2020 16:13,       India, 1, 10, 0
    3/17/2020 16:36,       India, 1, 10, 0
    3/17/2020 17:02,       India, 1, 10, 1
    3/17/2020 18:42,       India, 1, 10, 5
    3/17/2020 18:55,       India, 1, 10, 0
    3/17/2020 19:05,       India, 1, 10, 0
    3/17/2020 19:24,       India, 1, 10, 0
    3/17/2020 19:30,       India, 1, 10, 0
    3/17/2020 20:01,       India, 1, 10, 1
    3/17/2020 21:24,       India, 1, 10, 3
    3/17/2020 21:26,       India, 1, 10, 0
    3/17/2020 21:48,       India, 1, 10, 0
];

Left Join(tab1)
LOAD BaseTm, Count(BaseTm) As O1, Min(ID) As Min_ID
Resident tab1
Group By BaseTm
;

Left Join(tab1)
LOAD ID, BaseTm, If(Min_ID=ID, O1,0) As Output
Resident tab1;

[Saravanan_Desingh]

Output: