IntervalMatch on with 3 keys

[alexandermllr]

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!

[swuehl · Accepted solution]

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]

Thank you! It worked perfectly!