topic IntervalMatch on with 3 keys in QlikView

IntervalMatch on with 3 keys

alexandermllr — Wed, 14 Sep 2016 07:58:59 GMT

Hello,

i have a problem with Intervalmatch. I have multiple keys to get a price for a product.

Example:

LOAD * INLINE
[

Product, Date, Weight

1, 15/01/2010, 750

1, 02/01/2010, 1500

2, 01/04/2010, 857

];

FactsTable:
LOAD * INLINE
[

     Product, Price, Start, End, Weight_from, Weight_to
     1, 500, 01/01/2010, 31/01/2010, 0, 1000
     1, 800, 01/01/2010, 31/01/2010, 1001, 2000
     2, 1000, 01/01/2010, 31/12/2010, 0, 1500

2, 1100, 01/01/2011, 31/12/2011, 0, 1500

];

Match:

IntervalMatch (Date, Product) load Start, End, Product

I only have a problem with matching the Weight to the Product.

I hope you can help me!

Re: IntervalMatch on with 3 keys

swuehl — Wed, 14 Sep 2016 18:40:17 GMT

Maybe using something like

Set DateFormat = 'DD/MM/YYYY';

Table1:

LOAD * INLINE

[

Product, Date, Weight

1, 15/01/2010, 750

1, 02/01/2010, 1500

2, 01/04/2010, 857

];

FactsTable:

JOIN

LOAD * INLINE

[

Product, Price, Start, End, Weight_from, Weight_to

1, 500, 01/01/2010, 31/01/2010, 0, 1000

1, 800, 01/01/2010, 31/01/2010, 1001, 2000

2, 1000, 01/01/2010, 31/12/2010, 0, 1500

2, 1100, 01/01/2011, 31/12/2011, 0, 1500

];

RESULT:

LOAD Product, Date, Weight, Price

RESIDENT Table1

WHERE Date <= End and Date >= Start and Weight <= Weight_to and Weight >= Weight_from;

DROP Table Table1;

Re: IntervalMatch on with 3 keys

alexandermllr — Thu, 15 Sep 2016 07:15:33 GMT

Thank you! It worked perfectly!