Measure calculation and report format

[qlikviewwizard]

Hi All,

I was struct to calculate the salary for all the employees.

We have the heirarchy like  Incharge1>Incharge2>Emp.

1)  For ENAME1, As StartDate>EndDate need to show ENAME1's SAL = 0 even the source value is 1000.And then 1000 value need to add his/her Incharges ENAME5 and ENAME9.

2) ENAME5 SAL is 6000 and ENAME9 SAL is 3000. So need to add ENAME1 SAL 1000 to both.

New SAL of ENAME5 and ENAME9 would be 6000+1000 and 3000+1000

3) StartDate< EndDate then ENAME SAL will be remain same.

How to achieve the report format shown below. Please help.

Attached the source data and QVW file.

Source Data:

Target Report Format:

Thanks in advance.

[qlikviewwizard]

Hi All,

I used the below script. Now I am able to get the details of EMP, Incharge1 and Incharge2.

Script:

EMP:

LOAD Dept,

    EMPNO,

    ENAME,

    Incharge1,

    Incharge2,

    SAL,

    Date(StartDate) AS StartDate,

    Date(EndDate) AS  EndDate ,

    if (StartDate>EndDate,1,0) as Flag

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source);

Incharge1:

Load

Incharge1 as Incharge1,

sum(SAL) as SAL1

Resident EMP

where StartDate>EndDate

group by Incharge1;

Incharge2:

Load

Incharge2 as ENAME,

sum(SAL) as SAL2

Resident EMP

where StartDate>EndDate

group by Incharge2;

Please advise how to format this to my report format.

[jagan · Accepted solution]

Hi,

Try like this

Data:

LOAD * INLINE [

    Emp, Manager, Sal

    1, 4, 100

    2, 4, 200

    3, 4, 300

    4, , 500

];

Concatenate(Data)

LOAD

Manager AS Emp,

'' AS Manager,

Sal

RESIDENT Data

WHERE Len(Trim(Manager)) <> 0;

Regards,

Jagan.