Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

1 Solution

Accepted Solutions
morganaaron
Valued Contributor

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

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

4 Replies
sushil353
Honored Contributor II

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

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;

Not applicable

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

Hi Kumar,

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

ConsultantidDateKeyManagerid
102/01/2011101100
1010/01/2011102200
1020/02/2011103
2025/03/2011204400
2029/03/2011205
205/05/2011206

morganaaron
Valued Contributor

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

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

lusaxy81
New Contributor III

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

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;