Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the hierarchy Incharge1 > Incharge2 > Ename
If EndDt<StartDt then SAL x amount should go to each Incharge1 & Incharge2
If EndDt is NULL then SAL should go to Ename
My report should show like the below:
EMPNO Ename StartDt SAL
Sample Data:
How to achieve this? Please help me.
EMPNO | Ename | Incharge1 | Incharge2 | SAL | StartDt | EndDt |
1 | Ename1 | Ename5 | Ename12 | 1000 | 12-May-15 | 11-May-15 |
2 | Ename2 | Ename5 | Ename12 | 2000 | 11-May-15 | 3-May-15 |
3 | Ename3 | Ename5 | Ename12 | 3000 | 10-May-15 | |
4 | Ename4 | Ename5 | Ename12 | 1000 | 9-May-15 | |
5 | Ename5 | Ename5 | 2000 | 8-May-15 | ||
6 | Ename6 | Ename5 | Ename12 | 3000 | 7-May-15 | |
7 | Ename7 | Ename11 | Ename12 | 1000 | 12-May-15 | 10-May-15 |
8 | Ename8 | Ename11 | Ename12 | 2000 | 11-May-15 | 3-May-15 |
9 | Ename9 | Ename11 | Ename12 | 3000 | 10-May-15 | |
10 | Ename10 | Ename11 | Ename12 | 1000 | 9-May-15 | |
11 | Ename11 | Ename11 | Ename12 | 2000 | 8-May-15 | |
12 | Ename12 | Ename11 | Ename12 | 3000 | 12-May-15 | 11-May-15 |
13 | Ename13 | Ename11 | Ename12 | 1000 | 11-May-15 | |
14 | Ename14 | Ename11 | Ename9 | 2000 | 10-May-15 | |
15 | Ename15 | Ename11 | Ename9 | 3000 | 12-May-15 | |
16 | Ename16 | Ename11 | Ename9 | 1000 | 11-May-15 | 10-May-15 |
17 | Ename17 | Ename11 | Ename9 | 2000 | 12-May-15 | |
18 | Ename18 | Ename11 | Ename9 | 3000 | 11-May-15 | |
19 | Ename19 | Ename11 | Ename9 | 1000 | 12-May-15 | |
20 | Ename20 | Ename11 | Ename9 | 2000 | 11-May-15 |
t1:
LOAD EMPNO,
Ename,
Incharge1,
Incharge2,
SAL,
StartDt,
EndDt
FROM
[163767.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
//If EndDt<StartDt then SAL x amount should go to each Incharge1 & Incharge2
//If EndDt is NULL then SAL should go to Ename
t2:
load if(EndDt<StartDt,EMPNO) as empno,
if(EndDt<StartDt,Incharge1) as ename,
StartDt,
if(EndDt<StartDt,SAL) as SAL
resident t1
;
t3:
load if(EndDt<StartDt,EMPNO) as empno,
if(EndDt<StartDt,Incharge2) as ename,
StartDt,
if(EndDt<StartDt,SAL) as SAL
resident t1;
t4:
load
if(isnull(EndDt),EMPNO) as empno,
if(isnull(EndDt),Ename) as ename,
StartDt,
if(isnull(EndDt),SAL) as SAL
resident t1;
drop table t1
You could use something like the following
CrTmp:
Crosstable (Field,Ename,1) LOAD EMPNO,
if(Trim(Len(EndDt))=0,Ename) as Ename,
if(Trim(Len(EndDt))>0,if(Date#(EndDt,'DD-MMM-YY')<Date#(StartDt,'DD-MMM-YY'), Incharge1)) as Incharge1,
if(Trim(Len(EndDt))>0,if(Date#(EndDt,'DD-MMM-YY')<Date#(StartDt,'DD-MMM-YY'), Incharge2)) as Incharge2
FROM
[https://community.qlik.com/thread/163767]
(html, codepage is 1252, embedded labels, table is @1);
Enames:
Noconcatenate Load * Resident CrTmp;
Drop table CrTmp;
Data:
LOAD EMPNO,
SAL,
StartDt
FROM
[https://community.qlik.com/thread/163767]
(html, codepage is 1252, embedded labels, table is @1);
You can join the tabless and and remove Field Field, but I left it in for checking, see also attached
t1:
LOAD EMPNO,
Ename,
Incharge1,
Incharge2,
SAL,
StartDt,
EndDt
FROM
[163767.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
//If EndDt<StartDt then SAL x amount should go to each Incharge1 & Incharge2
//If EndDt is NULL then SAL should go to Ename
t2:
load if(EndDt<StartDt,EMPNO) as empno,
if(EndDt<StartDt,Incharge1) as ename,
StartDt,
if(EndDt<StartDt,SAL) as SAL
resident t1
;
t3:
load if(EndDt<StartDt,EMPNO) as empno,
if(EndDt<StartDt,Incharge2) as ename,
StartDt,
if(EndDt<StartDt,SAL) as SAL
resident t1;
t4:
load
if(isnull(EndDt),EMPNO) as empno,
if(isnull(EndDt),Ename) as ename,
StartDt,
if(isnull(EndDt),SAL) as SAL
resident t1;
drop table t1