Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
MVP
MVP

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;

View solution in original post

2 Replies
Highlighted
MVP
MVP

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;

View solution in original post

Highlighted
Creator
Creator

Thank you! It worked perfectly!