Interval match logic

[neelamsaroha157]

Hi,

I have a logic based on which I am trying to come up with the script. 

Please see the attached excel for sample data & logic.

Look back 30 days to startDate and see if there has been a MI data in that duration. If yes, Flag 1 else 0. --This part can be achieved by interval match but its creating a cartesian product as the dates are not are the same granularity.

Additional condition - If there is no MI date between current & previous start date and its still within 30 days of window then it should be flagged as 0.

Please let me know if you need additional information to help out.

Thank you for looking into it!!!

[Aurelien_Martinez · Accepted solution]

Hi,

I have this result:

With this script:

MI:
Load
ID,
Date([MI Date], 'DD/MM/YYYY') as [MI Date]
Inline [
ID, MI Date
1, 05/20/2022
1, 05/01/2022
1, 04/29/2022
1, 04/26/2022
1, 04/25/2022
1, 04/23/2022
1, 04/22/2022
1, 04/16/2022
1, 04/15/2022
2, 07/21/2022
2, 07/20/2022
3, 03/25/2022
3, 03/24/2022
3, 01/13/2022
3, 01/12/2022
3, 01/11/2022
3, 01/10/2022

];

data:
Load
ID,
Date(StartDate, 'DD/MM/YYYY') as StartDate
Inline [
ID, Unique ID, StartDate
1, 1, 05/30/2022
1, 1, 05/18/2022
1, 1, 05/03/2022
1, 1, 05/01/2022
1, 1, 02/10/2022
2, 2, 07/19/2022
2, 2, 07/21/2022
3, 3, 01/21/2022
3, 3, 04/15/2022
];

data_2:
Load
ID,
StartDate,
Peek(ID)=ID as TEST,
If(Peek(ID)=ID,
Date(Peek(StartDate)+1, 'DD/MM/YYYY'),
Date(StartDate-30, 'DD/MM/YYYY')
) as EndDate
Resident data
Order By
ID,
StartDate asc
;

Drop Table data;

Left Join(data_2)
IntervalMatch([MI Date], ID)
Load
EndDate,
StartDate,
ID
Resident data_2
;
Drop Tables MI;

data_f:
Load
ID,
StartDate,
If(Count([MI Date])>0, 1, 0) as FLAG
Resident data_2
Group By
ID,
StartDate
;

Drop Table data_2;

[neelamsaroha157]

Thank you @Aurelien_Martinez for your quick response. I'll validate it with real data today and will get back to you. Thank you so much again!!