Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AleRods88
Contributor III
Contributor III

Intervalmatch

Hello,

I have the following table

 

SHIPPERIODSTART_DATEEND_DATEVALUE
28038452018024/2/20183/2/20193
28038452018024/2/20193/1/20204
28038452018024/1/20201/3/20201
28038452018034/2/20183/2/20193
28038452018034/2/20193/1/20204
28038452018034/1/20201/3/20201
28038452018044/2/20183/2/20193
28038452018044/2/20193/1/20204
28038452018044/1/20201/3/20201
28038452018054/2/20183/2/20193
28038452018054/2/20193/1/20204
28038452018054/1/20201/3/20201
28038452018064/2/20183/2/20193
28038452018064/2/20193/1/20204
28038452018064/1/20201/3/20201
28038452018074/2/20183/2/20193
28038452018074/2/20193/1/20204
28038452018074/1/20201/3/20201
28038452018084/2/20183/2/20193
28038452018084/2/20193/1/20204
28038452018084/1/20201/3/20201
28038452018094/2/20183/2/20193
28038452018094/2/20193/1/20204
28038452018094/1/20201/3/20201
28038452018104/2/20183/2/20193
28038452018104/2/20193/1/20204
28038452018104/1/20201/3/20201
28038452018114/2/20183/2/20193
28038452018114/2/20193/1/20204
28038452018114/1/20201/3/20201
28038452018124/2/20183/2/20193
28038452018124/2/20193/1/20204
28038452018124/1/20201/3/20201
28038452019014/2/20183/2/20193
28038452019014/2/20193/1/20204
28038452019014/1/20201/3/20201
28038452019024/2/20183/2/20193
28038452019024/2/20193/1/20204
28038452019024/1/20201/3/20201
28038452019034/2/20183/2/20193
28038452019034/2/20193/1/20204
28038452019034/1/20201/3/20201
28038452019044/2/20183/2/20193
28038452019044/2/20193/1/20204
28038452019044/1/20201/3/20201
28038452019054/2/20183/2/20193
28038452019054/2/20193/1/20204
28038452019054/1/20201/3/20201
28038452019064/2/20183/2/20193
28038452019064/2/20193/1/20204
28038452019064/1/20201/3/20201
28038452019074/2/20183/2/20193
28038452019074/2/20193/1/20204
28038452019074/1/20201/3/20201
28038452019084/2/20183/2/20193
28038452019084/2/20193/1/20204
28038452019084/1/20201/3/20201
28038452019094/2/20183/2/20193
28038452019094/2/20193/1/20204
28038452019094/1/20201/3/20201
28038452019104/2/20183/2/20193
28038452019104/2/20193/1/20204
28038452019104/1/20201/3/20201
28038452019114/2/20183/2/20193
28038452019114/2/20193/1/20204
28038452019114/1/20201/3/20201
28038452019124/2/20183/2/20193
28038452019124/2/20193/1/20204
28038452019124/1/20201/3/20201
28038452020014/2/20183/2/20193
28038452020014/2/20193/1/20204
28038452020014/1/20201/3/20201
28038452020024/2/20183/2/20193
28038452020024/2/20193/1/20204
28038452020024/1/20201/3/20201
28038452020034/2/20183/2/20193
28038452020034/2/20193/1/20204
28038452020034/1/20201/3/20201

 

What I need to do is compare if the Period is in the range Start_Date/End_Date and bring a single row per Period with the Value associated to that Period:

SHIPPERIODSTART_DATEEND_DATEVALUE
28038452018024/2/20183/2/20193
28038452018034/2/20183/2/20193
28038452018044/2/20183/2/20193
28038452018054/2/20183/2/20193
28038452018064/2/20183/2/20193
28038452018074/2/20183/2/20193
28038452018084/2/20183/2/20193
28038452018094/2/20183/2/20193
28038452018104/2/20183/2/20193
28038452018114/2/20183/2/20193
28038452018124/2/20183/2/20193
28038452019014/2/20183/2/20193
28038452019024/2/20183/2/20193
28038452019034/2/20193/1/20204
28038452019044/2/20193/1/20204
28038452019054/2/20193/1/20204
28038452019064/2/20193/1/20204
28038452019074/2/20193/1/20204
28038452019084/2/20193/1/20204
28038452019094/2/20193/1/20204
28038452019104/2/20193/1/20204
28038452019114/2/20193/1/20204
28038452019124/2/20193/1/20204
28038452020014/1/20201/3/20201
28038452020024/1/20201/3/20201
28038452020034/1/20201/3/20201

 

Is there any way to do that?

Thank you!!

1 Reply
edwin
Master II
Master II

you dont need intervalmatch just use an if statement when loading:

=if(date(right('201802',2)&'/01/'&left('201802',4))>=startdate and date(right('201802',2)&'/01/'&left('201802',4))<=enddate, ....

note though that your period is month date so you need to define the business rules, will 02/2018 be in the period 02/15/2018 to 03/15/2018?  if so:

=if(date(right('201802',2)&'/01/'&left('201802',4))>=monthstart(startdate) and date(right('201802',2)&'/01/'&left('201802',4))<=monthend(enddate), ....