topic Re: Salary distribution in QlikView

Salary distribution

qlikviewwizard — Wed, 13 May 2015 08:50:25 GMT

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

Re: Salary distribution

stigchel — Wed, 13 May 2015 09:36:37 GMT

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

Re: Salary distribution

robert_mika — Wed, 13 May 2015 09:55:48 GMT

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