topic Re: How to Associate to the Closest Date in QlikView

How to Associate to the Closest Date

Thu, 02 Jul 2015 16:49:44 GMT

Hi all,

I have two date fields that I need to associate together to the nearest occurrence. We have OrderDate, which is when an order is actually placed, and HistoricalDate, associated to a change in Average Cost for the specific product.

For example:

ORDER TABLE COST TABLE

OrderID SkuID OrderDate SkuID HistoryDate Cost

1 123 1/1/2015 123 12/31/2014 $3.00

2 123 1/2/2015 123 1/3/2015 $3.50

3 123 1/3/2015

In this example, both OrderID's 1 and 2, should be associated with the cost of HistoryDate 12/31/2014, while Order 3 should be associated with the cost of HistoryDate 1/3/2015.

Any suggestions?

Re: How to Associate to the Closest Date

ramoncova06 — Thu, 02 Jul 2015 18:25:34 GMT

use intervalmatch

PFA

Re: How to Associate to the Closest Date

MK_QSL — Thu, 02 Jul 2015 18:32:09 GMT

like this?

Re: How to Associate to the Closest Date

Thu, 02 Jul 2015 20:41:40 GMT

Thanks, I wondered if I was going to have to create a range between the two dates!

Re: How to Associate to the Closest Date

MK_QSL — Fri, 03 Jul 2015 08:18:54 GMT

Hi Andrea,

Have you tested both solutions?