7 Replies Latest reply: Oct 25, 2012 4:12 AM by Staffan Johansson RSS

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

    rich calligan

      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.