topic Re: Hi All how to populate data for lower level record based on upper level of granularity ? in QlikView

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

shekhar_analyti — Wed, 31 Jan 2018 11:28:23 GMT

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

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

shekhar_analyti — Wed, 31 Jan 2018 11:47:55 GMT

stalwar1‌ .. tresesco Can above be achieved ?

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

sunny_talwar — Wed, 31 Jan 2018 12:04:26 GMT

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

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

shekhar_analyti — Wed, 31 Jan 2018 12:07:51 GMT

Hi Sunny ,

Can you just help me with solution for

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

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

sunny_talwar — Wed, 31 Jan 2018 12:14:45 GMT

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

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

shekhar_analyti — Wed, 31 Jan 2018 12:22:20 GMT

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

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

shekhar_analyti — Wed, 31 Jan 2018 12:24:08 GMT

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

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

sunny_talwar — Wed, 31 Jan 2018 12:28:00 GMT

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;

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

sunny_talwar — Wed, 31 Jan 2018 12:29:07 GMT

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

];

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

shekhar_analyti — Wed, 31 Jan 2018 12:35:24 GMT

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

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

sunny_talwar — Wed, 31 Jan 2018 12:38:29 GMT

Join and ApplyMap should still work... can you share the script that you are using right now?

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

shekhar_analyti — Thu, 01 Feb 2018 05:24:08 GMT

Hi Sunny ,

When i am selecting i am getting correct aggreagted value in table for 124 , but Zone value gets wronged , when i am unselecting 124 , zone value is correct but aggr value gets wrong

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

sunny_talwar — Thu, 01 Feb 2018 12:38:24 GMT

Like I mentioned, you need to use Hierarchy function here... not really good with it, but tried here a little. The result is not perfect, but see if you can play around with it to make it work for yourself