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.
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.
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.