Pivot table Query

[Arvind_Singh]

Hi All,

I am new to Qlik Sense and am looking for help with one of the requirements.
I need to create a Pivot table using the below data-

Emp Name	Points	Designation	Manager Name	Department	Vertical	Status
Alex		Manager		Sales	V1	Active
Andrew		Manager		Sales	V1	Inactive
John		Manager		Tech	V2	Active
Ross	2	Associate	Alex	Sales	V1	Active
James	1	Associate	Alex	Sales	V1	Active
Ryan	3	Associate	Andrew	Sales	V1	Active
Steve	2	Associate	Andrew	Sales	V1	Active
Gary	4	Associate	John	Tech	V2	Active
Peter	3	Associate	John	Tech	V2	Active

I need to build a Pivot table as  hierarchy=>   Vertical ->Department -> Manager Name ->Emp Name ->Points

Condition: If the status of the Manager is "Inactive" then the Pivot table should not include the Manager as well the the Employees under that Manager.

For example : Status of Andrew is Inactive in the above table so in the output pivot table, Andrew and Employees under him (Ryan and Steve) should not appear.

Expected Output - (Vertical ->Department -> Manager Name ->Emp Name ->Points)

V1
	Sales
		Alex
			Ross	2
			James	1
V2
	Tech
		John
			Gary	4
			Peter	3

[Kushal_Chawda · Accepted solution]

@Arvind_Singh  you can create flag in scripts for inactive manager and employees under them.

Data:
LOAD RowNo() as Row,
     [Emp Name], 
     Points, 
     Designation, 
     [Manager Name], 
     Department, 
     Vertical, 
     Status
FROM Table;

Left Join(Data)
LOAD [Emp Name] as [Manager Name],
     1 as InActiveManagerEmp
Resident Data
where Status='Inactive';

Now you can create the pivot table with required dimensions in hierarchy and use below measure

=sum({<Row={"=isnull(InActiveManagerEmp)"}>}Points)

If you have other measures in your pivot, you need to include the set in that measure as well.

Note: Go to pivot properties->Add-on-> Uncheck "Include zero Values" option

[Arvind_Singh]

Hi Kushal,
Thanks for the reply and proposing a solution. I tried the same Load script and measure, however in my Pivot I still see entry for Andrew and his employees. refer to the attached screenshot.

I would want the pivot to ignore the entries for Manager whose status is Inactive and the employees under him (in this case Andrew and Ryan, Steve)

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    Emp Name, Points, Designation, Manager Name, Department, Vertical, Status
    Alex,  , Manager,  , Sales, V1, Active
    Andrew,  , Manager,  , Sales, V1, Inactive
    John,  , Manager,  , Tech, V2, Active
    Ross, 2, Associate, Alex, Sales, V1, Active
    James, 1, Associate, Alex, Sales, V1, Active
    Ryan, 3, Associate, Andrew, Sales, V1, Active
    Steve, 2, Associate, Andrew, Sales, V1, Active
    Gary, 4, Associate, John, Tech, V2, Active
    Peter, 3, Associate, John, Tech, V2, Active
];

tab2:
NoConcatenate
LOAD DISTINCT Vertical As Child, 'Root' As Parent
Resident tab1; 
Concatenate(tab2)
LOAD DISTINCT Department As Child, Vertical As Parent
Resident tab1;
Concatenate(tab2)
LOAD DISTINCT [Manager Name] As Child, Department As Parent
Resident tab1
Where Not Len(Trim([Manager Name]))=0;
Concatenate(tab2)
LOAD DISTINCT [Emp Name] As Child, [Manager Name] As Parent
Resident tab1
Where Not Len(Trim([Manager Name]))=0;
Concatenate(tab2)
LOAD DISTINCT Points As Child, [Emp Name] As Parent
Resident tab1
Where Not Len(Trim(Points))=0
; 

tab3:
Hierarchy(Child, Parent, NodeName, ParentName, NodeName, PathName, '/', Depth)
LOAD *, Child As NodeName
Resident tab2;

Drop Table tab1, tab2;

[Saravanan_Desingh]

Output:

[Saravanan_Desingh]

Missed the Inactive part. Try this,

tab1:
LOAD * INLINE [
    Emp Name, Points, Designation, Manager Name, Department, Vertical, Status
    Alex,  , Manager,  , Sales, V1, Active
    Andrew,  , Manager,  , Sales, V1, Inactive
    John,  , Manager,  , Tech, V2, Active
    Ross, 2, Associate, Alex, Sales, V1, Active
    James, 1, Associate, Alex, Sales, V1, Active
    Ryan, 3, Associate, Andrew, Sales, V1, Active
    Steve, 2, Associate, Andrew, Sales, V1, Active
    Gary, 4, Associate, John, Tech, V2, Active
    Peter, 3, Associate, John, Tech, V2, Active
];

map:
Mapping
LOAD DISTINCT [Emp Name], '~'
Resident tab1
Where Status='Inactive';

tab2:
NoConcatenate
LOAD DISTINCT Vertical As Child, 'Root' As Parent
Resident tab1; 
Concatenate(tab2)
LOAD DISTINCT Department As Child, Vertical As Parent
Resident tab1;
Concatenate(tab2)
LOAD DISTINCT [Manager Name] As Child, Department As Parent
Resident tab1
Where Not Len(Trim([Manager Name]))=0;
Concatenate(tab2)
LOAD DISTINCT [Emp Name] As Child, [Manager Name] As Parent
Resident tab1
Where Not Len(Trim([Manager Name]))=0;
Concatenate(tab2)
LOAD DISTINCT Points As Child, [Emp Name] As Parent
Resident tab1
Where Not Len(Trim(Points))=0
; 

tab3:
Hierarchy(Child, Parent, NodeName, ParentName, NodeName, PathName, '/', Depth)
LOAD *, Child As NodeName
Resident tab2;

tab4:
NoConcatenate
LOAD *
Where Not Index(C1,'~');
LOAD *, MapSubString('map',PathName) As C1
Resident tab3
;

Drop Table tab1, tab2, tab3;

[Saravanan_Desingh]

Output:

[Kushal_Chawda]

@Arvind_Singh  If you are following the same script and logic provided, you should be able to get what you want.

Make sure that while joining you are joining on correct field which is manager name.

The script is working as expected on sample data provided at my end. 

[Kushal_Chawda]

@Arvind_Singh  as I mentioned earlier , if you have other measures in your pivot, you need to put the same set analysis in that measure. Also, follow all the steps correctly from my previous reply

[Arvind_Singh]

Thanks alot Kushal. The solution worked.