Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
michielvandegoo
Valued Contributor

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
Contributor III

Re: Interval match 2 dates and a range

Please find the attached file

7 Replies

Re: Interval match 2 dates and a range

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
michielvandegoo
Valued Contributor

Re: Interval match 2 dates and a range

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

Re: Interval match 2 dates and a range

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

Re: Interval match 2 dates and a range

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

michielvandegoo
Valued Contributor

Re: Interval match 2 dates and a range

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?

Re: Interval match 2 dates and a range

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
Contributor III

Re: Interval match 2 dates and a range

Please find the attached file

Community Browser