topic Re: Interval match 2 dates and a range in QlikView

Interval match 2 dates and a range

Michiel_QV_Fan — Wed, 19 Mar 2014 12:52:40 GMT

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

Re: Interval match 2 dates and a range

Gysbert_Wassenaar — Wed, 19 Mar 2014 13:12:53 GMT

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.

Re: Interval match 2 dates and a range

Michiel_QV_Fan — Wed, 19 Mar 2014 21:59:38 GMT

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

MarcoWedel — Thu, 20 Mar 2014 00:26:52 GMT

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

Date	amount
24-2-2013	700
30-4-2014	599

tables to reference for script tests

Re: Interval match 2 dates and a range

MarcoWedel — Thu, 20 Mar 2014 00:54:19 GMT

Hi Michiel,

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

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

Re: Interval match 2 dates and a range

Michiel_QV_Fan — Mon, 24 Mar 2014 07:58:17 GMT

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

MarcoWedel — Mon, 24 Mar 2014 08:03:38 GMT

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

Re: Interval match 2 dates and a range

arsal_90 — Mon, 24 Mar 2014 08:53:57 GMT

Please find the attached file