Salary distribution

[qlikviewwizard]

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

[stigchel]

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

[robert_mika · Accepted solution]

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