Relationship based in "equal or lower" value field

[#1]

Hello.  I´m new in QlikView, and I get the following issue:

I had 2 tables, with this info:

[SALES]

Date       | Client | Amount | ...

2013-11-01     01     $ 300

2013-11-02     23     $ 343

2013-12-23     34     $ 233

[OFFICERS]

Date        | Officer | Client |

2013-11-01       11       01

2013-11-01       34       02

2013-11-01       35       03

2013-12-01       45       01

2013-12-01       39       02

2013-12-01       34       03

And I need to match Sales with Officers.  The problem is that, the officers can change in time, between clients.

For example:

For Client=01, the Officer at 2013-11-01 was "11".  But for the same client, from 2013-12-01, the officer was "45"

I cant link the tables with "Date" column, because on [Officers] I had only the date from a new Officer is assigned.  So, I must search for the "nearest" date (equal or lower <= ).

How I can create a "relationship" between [SALES] and [OFFICERS] based in a "equal or lower" correspondence in "Date" column?

Sorry if my explication was a bit large

Thanks in advance!!!

[swuehl · Accepted solution]

You can use an Intervalmatch to handle this slowly changing dimension:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Your script code might look like this:

[SALES]:

LOAD * INLINE [

Date      , Client , Amount

2013-11-01,     01,     300

2013-11-02,     23,     343

2013-12-23,     34,     233

];

[OFFICERS_TMP]:

LOAD * INLINE [

Date        ,Officer , Client

2013-11-01,       11,       01

2013-11-01,       34,       02

2013-11-01,       35,       03

2013-12-01,       45,       01

2013-12-01,       39,       02

2013-12-01,       34,       03

];

OFFICERS:

LOAD Date as DateFrom,

           if(peek(Client) = Client, date(previous(Date)-1), date(makedate(2099))) as DateTo,

           Client,

           Officer

Resident OFFICERS_TMP order by Client, Date desc;

drop table OFFICERS_TMP;

IntervalMatch:

Inner Join

IntervalMatch(Date, Client) LOAD DateFrom, DateTo, Client Resident OFFICERS;

[#3]

Thank you very much swuehl !!

I will try this method.

Thanks again! 

[#4]

Hi.  I tested the script and works perfect.  It's just what I need. 

Sincerely, thank you very much!