IntervalMatch without an Ending Date (or max value) in match table

[Rich-HHE]

For the purpose of this question, I am providing a small, simple example. One is a table with sale transactions made to customers (Sales) and the other is a table with transactions of the company buying the product from the vendor (Purchase). I want to join our purchase information with the sale information so each sale will have our cost and purchase date with it.

There is no end date for these product prices (it is effective until the next purchase of the product). The cost record to be associated with the sale should be the most recent record in the purchase table relative to the sale date.

purchase: load * inline

[SKU, PurchaseDate, PurchasePrice

101, 1/15/2012, 1.15

102, 2/13/2012, 5.05

102, 6/22/2012, 6.17

101, 8/02/2012, 2.38

];

sales: load * inline

[SKU, SaleDate, SalePrice

101, 1/19/2012, 4.25

101, 1/30/2012, 4.25

102, 4/15/2012, 14.50

101, 5/01/2012, 4.75

101, 7/14/2012, 4.75

102, 7/16/2012, 17.50

102, 8/15/2012, 17.50

101, 8/19/2012, 5.75

];

The result should be:

SKU, SaleDate,  SalePrice, PurchaseDate, PurchasePrice

101, 1/19/2012, 4.25,      1/15/2012,    1.15

101, 1/30/2012, 4.25,      1/15/2012,    1.15

102, 4/15/2012, 14.50,     2/13/2012,    5.05

101, 5/01/2012, 4.75,      1/15/2012,    1.15

101, 7/14/2012, 4.75,      1/15/2012,    1.15

102, 7/16/2012, 17.50,     6/22/2012,    6.17

102, 8/15/2012, 17.50,     6/22/2012,    6.17

101, 8/19/2012, 5.75,      8/02/2012,    2.38

I have searched high and low and read dozens of discussions about IntervalMatch and have not found anyone asking anything similar to this. I apologize if I've missed it somewhere.

I will also want to use the answer to this to attribute the most appropriate direct marketing piece that was mailed to each customer (millions of records) to each sale that was made (millions of records), so any advice on the best and most efficient way to do this is greatly appreciated.

It feels like IntervalMatch may not be what I need for this. I'm farily new to QlikView so if I'm off, I'd greatly appreciate being pointed in the right direction.

Thanks for your time.

[whiteline]

You have to calculate the end date.

It's not so complicated as you have purchase dates.

[Rich-HHE]

How would you suggest I go about calculating the ending dates?

[whiteline]

I don't know how. Its your business field. You said:

it is effective until the next purchase of the product

Try to work in this direction. In case tha date is somewhere in the future use 01/01/2100 for example.

But if you want to use IntervalMatch you have to do it.

[Rich-HHE]

But if you want to use IntervalMatch you have to do it.

That's what I was afraid of. Not because calculating the ending effective date of a table of purchases would be difficult, but because there are other projects where I will need to relate a table on a "nearest match" where I'm matching 15M records to 36M records.

Does anyone know how to set up a fuzzy relationship between two tables? For example, in dBase for DOS (what I currently work in) you can SET NEAR ON and all relationships will be set to "nearest match." The reason I'm not doing this in dBase is because it takes 3-4 days to run each time and I'm hoping to move my analytical programs into QlikView for quicker updates times.

Edit: And I know how in theory to calculate the next date. I just don't know functionally in QlikView. I could easily write a program in dBase to go through and calculate the effective ending date but again, this would take too long.

[whiteline]

I think you can try to implement your logics manualy taking into account your restrictions.

IntervalMatch just saves time sometimes.

You can always use firstsortedvalue() or order your tables and use first/lastvalue functions.

[Rich-HHE]

Thanks. If anyone else has input, I'd be glad to hear it. I will chug away at it and post my working script here (if I ever get one) for others to reference.

[stabben23 · Accepted solution]

Hi Rich,

Try this to solve end date.

Temp:
Load
*,
PurchaseDate as StartDate,
    if(PurchaseDate<Previous(PurchaseDate),Previous(PurchaseDate)-1, PurchaseDate) as EndDate,
    resident purchase
    Order by SKU,PurchaseDate desc
     ;

//some kind of join

Intervalmatch(SaleDate, SKU)
Load StartDate, EndDate, SKU Resident Temp;