Find matching data in interval

[#1]

Dear all,

I have 2 tables: One with purchase data and a pricelist. The purchasedata now needs to be matched with the pricelist, based on the from-till data. Probably this can be done with interval, but I do not know where to start...

Please see attached for both files.

Thanks in advance for the help!

[#2]

Forgot the 2nd file

[Gysbert_Wassenaar]

To begin with you might want to clean up your pricelist. There are a lot of overlapping intervals in it. And the dates are strings, not numeric dates. If you can't change that in the pricelist you'll need to use the date# function in the load statement to make dates from the strings.

[marcus_malinow]

Gysbert is right - your pricelist data needs cleaning. You have overlapping intervals, and I think dates where no price is valid.

However, here's a script which may help:

LOAD Received,

     Item,

     ReceiptDate,

     Ordernr

FROM

Purchase.xlsx

(ooxml, embedded labels, table is Sheet1);



Pricelist:

LOAD Item,

     Price,

     DateFrom,

     DateTill

FROM
Pricelist.xlsx

(ooxml, embedded labels, table is Sheet1);



INNER JOIN INTERVALMATCH (ReceiptDate, Item) LOAD DateFrom, DateTill, Item RESIDENT Pricelist;



JOIN (Purchase) LOAD * RESIDENT Pricelist;

DROP TABLE Pricelist;

[Anonymous]

Hi Nienke,

You are right about the Interval. But you will need the extended Syntax for that.

Try using:

inner Join IntervalMatch(  ReceiptDate, Item ) LOAD DateFrom, DateTill, Item Resident Prices;

Please, see attached

Regards