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

[shekhar_analyti]

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

[shekhar_analyti]

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]

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]

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]

ID	Region
1	a
2	b
3	a
4	c

SSN	Reports_to_ID
4	2
5	3
6	3
7	1
8	4

Output

SSN	Zone
4	b
5	a
6	a
7	a
8	c

[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]

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