Mapping Load Challenge

[racer25]

Hi 

I have a user table and within that user table there is always a supervisor. That supervisor is also a user and equally has a supervisor until you get to the boss. 

I thought this should be a simple load and either a mapping load or left join but I am yet to get the right answer and reviewing the data that is not the issue.

I have put below a BEFORE and OUTCOME table.

USR_USERID	USR_NAME	USR_SUPERVISOR
ABC123	Worker	ABC456
ABC456	Manager	ABC999
ABC999	Senior Manager	ABC999
OUTCOME
USR_USERID	USR_NAME	MANAGER 1	MANAGER 2
ABC123	Worker	Manager	Senior Manager
ABC456	Manager	Senior Manager	Senior Manager
ABC456	Senior Manager	Senior Manager	Senior Manager

I did start with 

TEMP:
LOAD USR_USERID,
USR_NAME,
USR_SUPERVISOR as Manager1
FROM
[C:\Qlikview\In Development\USER TABLE.xlsx](ooxml, embedded labels, table is Sheet1);

\\ Load Table

LOAD
USR_SUPERVISOR as USR_USERID,
USR_SUPERVISOR as Manager2
FROM
[C:\Qlikview\In Development\USER TABLE.xlsx]
(ooxml, embedded labels, table is Sheet1);

\\ Get next Manager

I am clearly doing something quite basic incorrect but can't see what.

Any help would be appreciated.

Thanks,

Rob

[chrismarlow · Accepted solution]

Hi,

You need to add the supervisors ID when you join to iterate up - then join to that, something like;

data:
LOAD * INLINE [
USR_USERID, USR_NAME, USR_SUPERVISOR
ABC123, Worker, ABC456
ABC456, Manager, ABC999
ABC999, Senior Manager, ABC999
];

left join (data)
load
	USR_USERID AS USR_SUPERVISOR, 
	USR_NAME AS Manager1, 
	USR_SUPERVISOR AS USR_SUPERVISOR2
resident data;

left join (data)
load
	USR_USERID AS USR_SUPERVISOR2, 
	USR_NAME AS Manager2
resident data;

drop fields USR_SUPERVISOR2;

Cheers,

Chris.

[racer25]

Thanks Chris worked a treat on the real data.