Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
My requirement is to find 7th previous day for each date for an ID. An id can also not have 7 transactions, in that case it can consider available count of days until 7 transactions are available.
For example, my current dataset
ID | TRANSACTION_DATE | Expected Output |
A | 01-Apr-2022 | NULL |
A | 04-Apr-2022 | 01-Apr-2022 |
A | 05-Apr-2022 | 04-Apr-2022 |
A | 06-Apr-2022 | 05-Apr-2022 |
A | 07-Apr-2022 | 06-Apr-2022 |
A | 08-Apr-2022 | 07-Apr-2022 |
A | 11-Apr-2022 | 08-Apr-2022 |
A | 12-Apr-2022 | 01-Apr-2022 |
A | 13-Apr-2022 | 04-Apr-2022 |
A | 14-Apr-2022 | 05-Apr-2022 |
A | 19-Apr-2022 | 06-Apr-2022 |
A | 20-Apr-2022 | 07-Apr-2022 |
A | 21-Apr-2022 | 08-Apr-2022 |
A | 22-Apr-2022 | 11-Apr-2022 |
A | 25-Apr-2022 | 12-Apr-2022 |
A | 26-Apr-2022 | 13-Apr-2022 |
A | 27-Apr-2022 | 14-Apr-2022 |
A | 28-Apr-2022 | 19-Apr-2022 |
A | 29-Apr-2022 | 20-Apr-2022 |
B | 01-Apr-2022 | NULL |
B | 04-Apr-2022 | 01-Apr-2022 |
Hi, you can use Peek() to access previously loaded rows, it could be something like:
LOAD
ID,
TRANSACTION_DATE,
If(ID=Peek(ID)
,If(not IsNull(Peek(TRANSACTION_DATE,7) and ID=Peek(ID,7)
,Peek(TRANSACTION_DATE,7)
,Peek(TRANSACTION_DATE))
,Null()) as OTHER_DATE
Hi, you can use Peek() to access previously loaded rows, it could be something like:
LOAD
ID,
TRANSACTION_DATE,
If(ID=Peek(ID)
,If(not IsNull(Peek(TRANSACTION_DATE,7) and ID=Peek(ID,7)
,Peek(TRANSACTION_DATE,7)
,Peek(TRANSACTION_DATE))
,Null()) as OTHER_DATE