Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the below sample data displaying the L1, L2 & L3 hierarchies
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 |
The requirement is to to transform the above table into the 2 tables mentioned below. Can someone please help me with this?
Table 1:
Manager | Emp |
A | A |
A | B |
A | C |
A | D |
A | E |
A | F |
A | G |
A | H |
A | I |
A | J |
A | K |
B | B |
B | F |
B | G |
C | C |
C | H |
D | D |
D | I |
E | E |
E | J |
E | K |
Table 2:
Manager | Emp |
A | B |
A | C |
A | D |
A | E |
A | F |
A | G |
A | H |
A | I |
A | J |
A | K |
B | F |
B | G |
C | H |
D | D |
D | I |
E | J |
E | K |
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='');
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