Up One Level Hierarchy

mwscott1 — Wed, 25 Nov 2020 16:16:04 GMT

Based on a individual persons code (AGT_6) I am trying to return the office number for the next level of management. Starting at the bottom level AAA the next level up would be UNT. However, in some cases the UNT will be nul and it would role up to the MGR. Also, in other cases the person would already be at one of the higher levels and it would role up to the level above them. So regardless of the level of management I need to return the next level above.

The AAA is the bottom and has no office number. The green number would be the individuals office number and the yellow number would be the next level up that I need to return for each individual AGT_6 number.

AGT_6	Contract Type	UNT	MGR	TERR	RGN
759587	AAA	00A475520000	00A450030000	00A450000000	00V540010000
AGT_6	Contract Type	UNT	MGR	TERR	RGN
258746	UUU	00A596340000	00A698570000	00A3958740000	00V365874000

AGT_6	Contract Type	UNT	MGR	TERR	RGN
258746	MMM	nul	00A698570000	00A3958740000	00V365874000
AGT_6	Contract Type	UNT	MGR	TERR	RGN
175962	TTT	nul	nul	00A369700000	00V692710000

This would be the load statement I have to load all the data, What I want to be able to do is when i search by the AGT_6 I return the next level office number for that AGT_6.

SQL SELECT substr(AGT_CD,3,6) as AGT_6,
“MGR_OFF_NO",
"RGN_OFF_NO",
"TERR_OFF_NO",
"UNT_OFF_NO"
FROM ABCD.MAGIC;

topic Up One Level Hierarchy in QlikView

Up One Level Hierarchy