To find 7th previous date for each date in script

AV1 — Tue, 31 May 2022 12:19:31 GMT

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

Re: To find 7th previous date for each date in script

rubenmarin — Tue, 31 May 2022 16:16:43 GMT

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

topic Re: To find 7th previous date for each date in script in App Development

To find 7th previous date for each date in script

Re: To find 7th previous date for each date in script