Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

alexandermllr
Not applicable

IntervalMatch on with 3 keys

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!

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: IntervalMatch on with 3 keys

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;

2 Replies
swuehl
Not applicable

Re: IntervalMatch on with 3 keys

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;

alexandermllr
Not applicable

Re: IntervalMatch on with 3 keys

Thank you! It worked perfectly!