Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

Hi All how to populate data for lower level record based on upper level of granularity ?

Hi All ,

Hi All how to populate data for lower level record based on upper level of granularity ?

But i want to filter those SSN which are reporting to someone and few others are reporting to those SSN as well . For these SSN , Zone does not exist , but i want their Zone be populated as Zone of the person , whom these SSN are reporting to

Please find attached excel sheet with data and output required .  I have output1 and output2 as well .



Thanks & Regards

Shekar

Message was edited by: shekar shekhar

12 Replies
shekhar_analyti
Specialist
Specialist
Author

stalwar1‌ .. tresesco Can above be achieved ?

sunny_talwar

Almost seem like you need Hierarchy function easy and Efficient with example--- here to get this done.... I have not used this function too much to be of help, but I know marcowedel‌ has posted solution related to Hierarchy... so may be he can offer help

shekhar_analyti
Specialist
Specialist
Author

Hi Sunny ,

Can you just help me with solution for

" Zone be populated as Zone of the person , whom these SSN are reporting to "

sunny_talwar

I am not sure I understand the statement... would you be able to elaborate using an example?

shekhar_analyti
Specialist
Specialist
Author

Ok ..

Let say that there are two ID 2 and 3 which is reporting to id 4 and region of id 4 is US , then a zone field should be populated for ID 2 and 3 with value as US

shekhar_analyti
Specialist
Specialist
Author

IDRegion
1a
2b
3a
4

c

  

SSNReports_to_ID
42
53
63
71
84

Output

 

SSNZone
4b
5a
6a
7a
8c
sunny_talwar

You can use join or ApplyMap here

Table1:

LOAD * INLINE [

    ID, Region

    1, a

    2, b

    3, a

    4, c

];


Table2:

LOAD * INLINE [

    SSN, Reports_to_ID

    4, 2

    5, 3

    6, 3

    7, 1

    8, 4

];


Left Join (Table2)

LOAD ID as Reports_to_ID,

Region as Zone

Resident Table1;

sunny_talwar

With MappingTable

MappingTable:

Mapping

LOAD * INLINE [

    ID, Region

    1, a

    2, b

    3, a

    4, c

];


Table2:

LOAD *,

ApplyMap('MappingTable', Reports_to_ID) as Zone;

LOAD * INLINE [

    SSN, Reports_to_ID

    4, 2

    5, 3

    6, 3

    7, 1

    8, 4

];

shekhar_analyti
Specialist
Specialist
Author

Thank You Sunny Bhai . Unfortunately i have table with multi columns . Very similar to data attached .