Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mwscott1
Creator
Creator

Up One Level Hierarchy

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;

0 Replies