Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
richcalligan
New Contributor III

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

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.

1 Solution

Accepted Solutions
stabben23
Honored Contributor

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

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;

7 Replies
whiteline
Honored Contributor II

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

You have to calculate the end date.

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

richcalligan
New Contributor III

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

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

whiteline
Honored Contributor II

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

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.

richcalligan
New Contributor III

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

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
Honored Contributor II

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

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.

richcalligan
New Contributor III

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

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
Honored Contributor

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

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;

Community Browser