Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Michiel_QV_Fan
Specialist
Specialist

Interval match 2 dates and a range

I need to match the following:

Date_from, Date_to, Start_amount, End_amount

1-1-2013, 31-12-2013, 0, 500

1-1-2013, 31-12-2013, 501, 1000

1-1-2014, 31-12-2014, 0, 600

1-1-2014, 31-12-2014, 601, 1100

Fact:

To a date and amount in my fact.

The result should show in which category the line in my fact belong. This can be a combined field from Start_amount & End_amount.

Result:

Fact:

24-2-2013, 700, 501-1000

30-4-2014, 599, 0-600

etc.

is this even possible with interval match, 2 ranges?

Thanks

1 Solution

Accepted Solutions
arsal_90
Creator III
Creator III

Please find the attached file

View solution in original post

7 Replies
Gysbert_Wassenaar

Yes, it's possible. But you first need to match one interval and store the result in a new table. You can then match that new table with the second interval.


talk is cheap, supply exceeds demand
Michiel_QV_Fan
Specialist
Specialist
Author

I created some kinda approach like that. Maybe not the most beautiful solution but it works. I will post this soon.

MarcoWedel

Date_fromDate_toStart_amountEnd_amount
1-1-201331-12-20130500
1-1-201331-12-20135011000
1-1-201431-12-20140600
1-1-201431-12-20146011100

Dateamount
24-2-2013700
30-4-2014599

tables to reference for script tests

MarcoWedel

Hi Michiel,

this is one occasion where you can use the extended syntax of the intervalmatch prefix like this:

QlikCommunity_Thread_111169_Pic1.JPG.jpg

tabCategory:

LOAD Date_from,

     Date_to,

     Start_amount,

     End_amount

FROM

[http://community.qlik.com/thread/111169]

(html, codepage is 1252, embedded labels, table is @1);

tabFacts:

LOAD Date,

     amount

FROM

[http://community.qlik.com/thread/111169]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (tabFacts) IntervalMatch (Date) LOAD distinct Date_from, Date_to Resident tabCategory;

Left Join (tabFacts) IntervalMatch (amount, Date_from, Date_to) LOAD distinct Start_amount, End_amount, Date_from, Date_to Resident tabCategory;

Left Join (tabFacts) LOAD Start_amount, End_amount, Start_amount&'-'&End_amount as amountCategory Resident tabFacts;

DROP Fields Date_from, Date_to, Start_amount, End_amount From tabFacts;

No additional tables needed

hope this helps

regards

Marco

Michiel_QV_Fan
Specialist
Specialist
Author

I have been testing this script. There is no link between the tabfacts and tabcategory. QlikView cannot handle all the possibilities , over 21.000.000 in my data in a table in the GUI.

There should be a (synthetic) key between the tables, right?

MarcoWedel

This calculation can take a while, yes.

There are no synthetic keys in the loaded tables in this case.

If You need those, then just skip the last drop statement.

regards

Marco

arsal_90
Creator III
Creator III

Please find the attached file