Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Thank you! It worked perfectly!