topic Re: Mapping Load Challenge in QlikView

Mapping Load Challenge

racer25 — Sat, 16 Nov 2024 19:44:28 GMT

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

Re: Mapping Load Challenge

chrismarlow — Wed, 06 Nov 2019 14:48:32 GMT

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.

Re: Mapping Load Challenge

racer25 — Wed, 06 Nov 2019 15:58:10 GMT

Thanks Chris worked a treat on the real data.