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