Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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 |
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
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;