most efficient way to return set from two tables where id match, and salesdate > effectivedate

[#1]

Can someone tell me the best way in Qlikview to get those 2 tables to return the results in Table 3.  In this scenario, the sales consultant changes the manager periodically and this is stored in Table 2.  The sales records contain the consultant id, date, etc.  The report should do analysis by consultant and the manager at the time the sale was recorded.

Table 1 Sales:

Date,  Consultantid

10/01/2011, 10

20/02/2011,10

25/03/2011,20

5/05/2011,20

Table 2 ConsultantManagerHistory.

Consultantid, Effectivedate, Managerid

10, 01/01/2011, 100

10, 01/02/2011, 200

20, 01/01/2011, 300

20, 01/05/2011,400

Table 3: the sales records to return the consultant manager id based on the effective date from the consultant history

Date,Consultantid, Managerid

01/01/2011, 10, 100

20/02/2011,10, 200

25/03/2011,20, 300

5/05/2011,20, 400

Best Regards

Simona

[sushil353]

hi,

try this

Temp:
LOAD Date(Date#(Date,'DD/MM/YYYY')) as Date,Consultantid Inline
[
Date,Consultantid
10/01/2011, 10
20/02/2011,10
5/05/2011,20
25/03/2011,20
];

NoConcatenate
A:
LOAD
Date,
Consultantid,
Consultantid&rowno() as Key
Resident Temp
Order by Consultantid,Date;

DROP Table Temp;

Temp:
LOAD Date(Date#(Effectivedate,'DD/MM/YYYY')) as Effectivedate,Consultantid as cid,Managerid Inline
[
Consultantid, Effectivedate, Managerid
10, 01/01/2011, 100
10, 01/02/2011, 200
20, 01/05/2011,400
20, 01/01/2011, 300
];

Left Join(A)
B:
LOAD
cid&RowNo() as Key,
Managerid
Resident Temp
Order by cid,Effectivedate;

DROP Table Temp;

[#3]

Hi Kumar,

This is not going to work, it returned blank manager ids, when I added more sales

Consultantid	Date	Key	Managerid
10	2/01/2011	101	100
10	10/01/2011	102	200
10	20/02/2011	103
20	25/03/2011	204	400
20	29/03/2011	205
20	5/05/2011	206

[morganaaron · Accepted solution]

Hi Simona,

IntervalMatch and Slowly Changing Dimensions is what you want to follow in order to achieve what you're after.

You first need to create an interval from your single dates and from there you can use intervalmatch to match the correct date to each interval.

As an example, I got yours to work using this script:

Let vEndDate = Num('01/06/2011');

Sales:
Load * Inline [
Date, Consultantid
10/01/2011, 10
20/02/2011, 10
25/03/2011, 20
05/05/2011, 20
];

History:
Load * Inline [
Consultantid, Effectivedate, Managerid
10, 01/01/2011, 100
10, 01/02/2011, 200
20, 01/01/2011, 300
20, 01/05/2011, 400
];

NoConcatenate
HistoryReverse:
Load *
Resident History
Order by Consultantid, Effectivedate desc;

Drop Table History;

Left Join(HistoryReverse)
Intervals:
Load
Consultantid,
Managerid,
Date(Effectivedate) as StartDate,
If(Consultantid=Peek(Consultantid), Date(Previous(Effectivedate)-1), Date($(vEndDate))) as EndDate
Resident HistoryReverse;

Drop Field Effectivedate;

Inner Join
IntervalMatch:
IntervalMatch(Date, Consultantid)
Load
StartDate,
EndDate,
Consultantid
Resident HistoryReverse;

But that's just an example and you may want to do it slightly differently. Happy to explain any point of the above if need be.

Aaron

[Anonymous]

Hi Simona,

I would create an interval table from your manager history table and then do an interval match (see 3rd table for creating the intervals):

SALES:

LOAD * INLINE [

    Date, ConsultantID,SalesAmount

    10/01/2011,10,100

    20/02/2011,10,290

    25/03/2011,20,200

    05/05/2011,20,150

];

TMANAGER:

LOAD * INLINE [

  ConsultantID, EffectiveDate, ManagerID

  10,01/01/2011,100

  10,01/02/2011,200

  20,01/01/2011,300

  20,01/05/2011,400

];

CONSULTANTMANAGERHISTORY:

load

  if(Previous(ConsultantID) = ConsultantID ,previous(EffectiveDate)) as EndDate,

  EffectiveDate as StartDate,

  ConsultantID,

  ManagerID

Resident TMANAGER ORDER BY ConsultantID ASC, EffectiveDate DESC;

Drop Table TMANAGER;