topic Re: Find matching data in interval in QlikView

Find matching data in interval

Thu, 07 Nov 2013 09:38:01 GMT

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!

Re: Find matching data in interval

Thu, 07 Nov 2013 09:38:49 GMT

Forgot the 2nd file

Re: Find matching data in interval

Gysbert_Wassenaar — Thu, 07 Nov 2013 10:46:25 GMT

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.

Re: Find matching data in interval

marcus_malinow — Thu, 07 Nov 2013 11:18:59 GMT

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;

Re: Re: Find matching data in interval

Anonymous — Thu, 07 Nov 2013 11:25:51 GMT

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