topic Re: Interval Match Question in QlikView

Interval Match Question

Wed, 05 Nov 2014 01:05:19 GMT

We have two tables. One table has columns 'ID' and 'Invoice Date'. Another one has columns 'ID', 'Start Date', 'End Date', and 'Price'. The desired output is 'ID', 'Invoice Date', 'Price'. I know this could be done using interval match but having a hard time figuring out how. Any help is highly appreciated. Attached is sample data for your convenience.

Table 1:

Product ID	InvoiceDate
A	10/16/2014
B	08/01/2014

Table 2:

Product ID	Start Date	End Date	Price
A	06/01/2014	06/30/2014	$200
A	07/01/2014	08/30/2014	$350
A	09/01/2014	12/31/9999	$400
B	06/01/2014	06/30/2014	$250
B	07/01/2014	08/30/2014	$240
B	09/01/2014	12/31/9999	$300

Desired Output:

Product ID	InvoiceDate	Price
A	06/02/2014	$200
A	07/20/2014	$350
B	06/20/2014	$250
B	09/26/2014	$300

Re: Interval Match Question

Anonymous — Wed, 05 Nov 2014 01:11:52 GMT

Hey,

PFA document would help you.

REgards,

Chinna.

Re: Interval Match Question

Wed, 05 Nov 2014 01:24:36 GMT

No Luck. How to fit in amount is confusing me. I would really appreciate if you can create a quick QVW with attached data and post it back. That will be very helpful in understanding it. Thanks in advance for helping.

Re: Interval Match Question

JonnyPoole — Wed, 05 Nov 2014 01:42:19 GMT

I started to do the interval match, and the first two IDs work as expected.

But then i noticed some duplication in your data that don't match the desired results.

For example, for 1000E2USdd 16890-000 , you have two intervales for 9/1/2014-> 12/31/9999 with 2 different values. See below

And when i check the desired result it doesn't quite make sense

How did you associate 9/26/2014 to line 11 and 10/15/2014 to line 14 ??

Re: Interval Match Question

Wed, 05 Nov 2014 05:35:49 GMT

I see what you are saying.Makes sense! I have updated the datasheet and have simplified it a bit. Please use that instead. Thanks for helping !

Re: Re: Interval Match Question

anbu1984 — Wed, 05 Nov 2014 08:59:41 GMT

Check this app

Re: Re: Interval Match Question

JonnyPoole — Wed, 05 Nov 2014 14:48:48 GMT

Ok - i still see an unexpected 'desired output' in your spreadsheet.

There are 2 records with invoice dates below

To match those 2 records to specific intervals we have the following possible intervals for A and B

According to the above ID=A and InvoiceDate=10/16/2014 would map to $400 and ID=B and InvoiceDate=08/01/2014 would map to 240$ . However your expected outcome is this ???

When I applied the interval match in QlikView i got the expected result of

If you agree with me, below is the load script for this and attached is the QVW. The solution uses the 'extended' interval match syntax to not only match the interval but another identifier as well... in this case ID.

Invoices:

LOAD ID,

Date(InvoiceDate) as InvoiceDate

FROM

(ooxml, embedded labels, table is [Table 1]);

StartEnds:

LOAD ID,

Date([Start Date]) as [Start Date],

Date([End Date]) as [End Date],

Price

FROM

(ooxml, embedded labels, table is [Table 2]);

inner join IntervalMatch (InvoiceDate,ID) LOAD [Start Date], [End Date],ID Resident StartEnds;

Re: Interval Match Question

hic — Thu, 06 Nov 2014 14:28:25 GMT

See also the QlikTech document about it: IntervalMatch and Slowly Changing Dimensions

HIC