Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER

Intervalmatch example – with dates

qlikviewwizard
Master II
Master II

Intervalmatch example – with dates

I have two tables Table1,Table2. Table1 has DATE, Table2 has START_DT,END_DT. Want to join these two tables on DATE columns.Intervalmatch function will help us to make the join in Qlik. If we use left join, we can avoid synthetic tables too. Here is the example.If you want to test, prepare the test data.
Let me know if you need any more info/clarification.
Table1:
LOAD COUNTRY,
DATE,
C1,
C2
FROM Table1;
Table2:
LOAD COUNTRY, C3,C4,
date(START_DT) as START_DT,
date(END_DT) as END_DT
FROM Table2;
Final_Table:
INTERVALMATCH (DATE) left JOIN LOAD START_DT, END_DT RESIDENT Table2;
left Join (Table1) LOAD distinct * RESIDENT Table2;
DROP TABLE Table2;

Labels (3)
Comments
guy_hombleu
Contributor III
Contributor III

Thanks for the example, works superb!

0 Likes
qlikviewwizard
Master II
Master II

@guy_hombleu

Nice to hear. Please mark the correct Answer.

0 Likes
sudeep78
Contributor II
Contributor II

Hi @qlikviewwizard ,

I came across this post while struggling to get Intervalmatch() to work in Qlik Sense. Your solution worked very well for me. Thank you for this.

Now for a follow-on question. Both data tables have millions of rows of data and I get error code 129 (out of memory) while executing the load script. Could you recommend an approach to use your solution for such large volumes of data?

Happy to provide additional details if it would help.

Thank you in advance,

Sudeep

0 Likes
Version history
Revision #:
2 of 2
Last update:
‎2019-10-22 10:16 PM
Updated by:
 
Contributors