Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
neelamsaroha157
Specialist II
Specialist II

Interval match logic

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!!!

Labels (2)
1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

 

I have this result:

amartinez35_0-1661939311717.png

 

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;

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

2 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

 

I have this result:

amartinez35_0-1661939311717.png

 

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;

Help users find answers! Don't forget to mark a solution that worked for you!
neelamsaroha157
Specialist II
Specialist II
Author

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!!