Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rishimessi19
Contributor II
Contributor II

Hierarchy in QlikView

Hi

I have the below sample data displaying the L1, L2 & L3 hierarchies

L1L2L3
A  
AB 
ABF
ABG
AC 
ACH
AD 
ADI
AD 
AEJ
AEK

 

The requirement is to to transform the above table into the 2 tables mentioned below. Can someone please help me with this?

Table 1:

ManagerEmp
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
BB
BF
BG
CC
CH
DD
DI
EE
EJ
EK

 

Table 2:

ManagerEmp
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
BF
BG
CH
DD
DI
EJ
EK

 

 

2 Replies
chrismarlow
Specialist
Specialist

Hi,

Think this is Table 1 - to get you started.

Cheers,

Chris.

data:
LOAD * INLINE [
L1, L2, L3
A,,
A, B,
A, B, F
A, B, G
A, C,
A, C, H
A, D,
A, D, I
A, D,
A, E, J
A, E, K
];

data_final:
Load Distinct
	L1 AS Manager,
	If(IsNull(L2) or L2='',L1,L2) AS Emp
Resident data;

Concatenate (data_final)
Load Distinct
	L1 AS Manager,
	L3 AS Emp
Resident data
where not (IsNull(L3) or L3='');

Concatenate (data_final)
Load Distinct
	L2 AS Manager,
	If(IsNull(L3) or L3='',L2,L3) AS Emp
Resident data
where not (IsNull(L2) or L2='');
Brett_Bleess
Support (Former)
Support (Former)

The only thing I have in addition to Chris' post is the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Unbalanced-n-level-hierarchies/ba-p/1474325

That might be useful to a degree.  If Chris' post got you on the right track, be sure to come back and use the Accept as Solution button on it to give them credit for the assistance and let other Community Members know it helped.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.