To find 7th previous date for each date in script

[AV1]

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

[rubenmarin · Accepted solution]

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