Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Output: