hierarchical data issue

[qvqfqlik]

Need some help with Hierarchy data.

I am able to get the Hierarchial names, but  Iam unable to get the the remaining fields data while still maintaining the hierarchy.

I have used

Hierarchy(ID_2,ID_3,NAME) , with this I am able to get the hierarchy names.

And for other data , I have been writing multiple Hierarchy functions like below

eg:  Hierarchy(ID_2,ID_3,Owner)

Hierarchy(ID_2,ID_3,StartDate)

Hierarchy(ID_2,ID_3,EndDate)

The data Iam getting is correct, but not sure if this an ideal way to write multiple Hierarchies for each fields, and it's also creating Synthetic keys.

Below is the sample data , and required result:

SampleData:

ID_1	ID_2	ID_3	NAME	ParentNameName	StartDate	Owner	EndDate
12648	14634	NULL	Grandparent_1	NULL	StartDate_Grandparent_1	Owner_1	EndDate_Grandparent_1
12649	14635	14634	Parent-1_OfGrandParent1	Grandparent_1	StartDateParent-1_OfGrandParent1	Owner_2	EndDateParent-1_OfGrandParent1
12548	14655	14634	Parent-2_OfGrandParent1	Grandparent_1	StartDate_Parent-2_OfGrandParent1	Owner_3	EndDate_Parent-2_OfGrandParent1
12651	14656	14634	Parent-3_OfGrandParent1	Grandparent_1	StartDate_Parent-3_OfGrandParent1	Owner_4	EndDate_Parent-3_OfGrandParent1
12653	14658	14656	Child-1_Of_Parent-3_OfGrandParent1	Parent-3_OfGrandParent1	StartDate_Child-1_Of_Parent-3_OfGrandParent1	Owner_5	EndDate_Child-1_Of_Parent-3_OfGrandParent1
12656	14661	14656	Child-2_Of_Parent-3_OfGrandParent1	Parent-3_OfGrandParent1	StartDate_Child-2_Of_Parent-3_OfGrandParent1	Owner_6	EndDate_Child-2_Of_Parent-3_OfGrandParent1
12658	14663	14656	Child-3_Of_Parent-3_OfGrandParent1	Parent-3_OfGrandParent1	StartDate_Child-3_Of_Parent-3_OfGrandParent1	Owner_7	EndDate_Child-3_Of_Parent-3_OfGrandParent1
12659	14664	14656	Child-4_Of_Parent-3_OfGrandParent1	Parent-3_OfGrandParent1	StartDate_Child-4_Of_Parent-3_OfGrandParent1	Owner_8	EndDate_Child-4_Of_Parent-3_OfGrandParent1
12660	14665	14656	Child-5_Of_Parent-3_OfGrandParent1	Parent-3_OfGrandParent1	StartDate_Child-5_Of_Parent-3_OfGrandParent1	Owner_9	EndDate_Child-5_Of_Parent-3_OfGrandParent1
13187	14716	NULL	Grandparent_2	NULL	StartDate_Grandparent_2	Owner_11	EndDate_Grandparent_2
13188	14717	14716	Parent-1_OfGrandParent2	Grandparent_2	StartDate_Parent-1_OfGrandParent2	Owner_12	EndDate_Parent-1_OfGrandParent2
13166	14737	14716	Parent-2_OfGrandParent2	Grandparent_2	StartDate_Parent-2_OfGrandParent2	Owner_13	EndDate_Parent-2_OfGrandParent2
13118	14738	14716	Parent-3_OfGrandParent2	Grandparent_2	StartDate_Parent-3_OfGrandParent2	Owner_14	EndDate_Parent-3_OfGrandParent2
13127	14747	14738	Child-1_Of_Parent-3_OfGrandParent2	Parent-3_OfGrandParent2	StartDate_Child-1_Of_Parent-3_OfGrandParent2	Owner_15	EndDate_Child-1_Of_Parent-3_OfGrandParent2

Required Result:

Grandparent_1		StartDate_Grandparent_1	StartDate_Grandparent_1	Owner_1	Owner_1	EndDate_Grandparent_1	EndDate_Grandparent_1
Parent-1_OfGrandParent1	Grandparent_1	StartDateParent-1_OfGrandParent1	StartDate_Grandparent_1	Owner_2	Owner_1	EndDateParent-1_OfGrandParent1	EndDate_Grandparent_1
Parent-2_OfGrandParent1	Grandparent_1	StartDate_Parent-2_OfGrandParent1	StartDate_Grandparent_1	Owner_3	Owner_1	EndDate_Parent-2_OfGrandParent1	EndDate_Grandparent_1
Parent-3_OfGrandParent1	Grandparent_1	StartDate_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_4	Owner_1	EndDate_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Child-1_Of_Parent-3_OfGrandParent1	Grandparent_1	StartDate_Child-1_Of_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_5	Owner_1	EndDate_Child-1_Of_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Child-2_Of_Parent-3_OfGrandParent1	Grandparent_1	StartDate_Child-2_Of_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_6	Owner_1	EndDate_Child-2_Of_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Child-3_Of_Parent-3_OfGrandParent1	Grandparent_1	StartDate_Child-3_Of_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_7	Owner_1	EndDate_Child-3_Of_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Child-4_Of_Parent-3_OfGrandParent1	Grandparent_1	StartDate_Child-4_Of_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_8	Owner_1	EndDate_Child-4_Of_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Child-5_Of_Parent-3_OfGrandParent1	Grandparent_1	StartDate_Child-5_Of_Parent-3_OfGrandParent1	StartDate_Grandparent_1	Owner_9	Owner_1	EndDate_Child-5_Of_Parent-3_OfGrandParent1	EndDate_Grandparent_1
Grandparent_2	Grandparent_2	StartDate_Grandparent_2	StartDate_Grandparent_2	Owner_11	Owner_1	EndDate_Grandparent_2	EndDate_Grandparent_2
Parent-1_OfGrandParent2	Grandparent_2	StartDate_Parent-1_OfGrandParent2	StartDate_Grandparent_2	Owner_12	Owner_1	EndDate_Parent-1_OfGrandParent2	EndDate_Grandparent_2
Parent-2_OfGrandParent2	Grandparent_2	StartDate_Parent-2_OfGrandParent2	StartDate_Grandparent_2	Owner_13	Owner_1	EndDate_Parent-2_OfGrandParent2	EndDate_Grandparent_2
Parent-3_OfGrandParent2	Grandparent_2	StartDate_Parent-3_OfGrandParent2	StartDate_Grandparent_2	Owner_14	Owner_1	EndDate_Parent-3_OfGrandParent2	EndDate_Grandparent_2
Child-1_Of_Parent-3_OfGrandParent2	Grandparent_2	StartDate_Child-1_Of_Parent-3_OfGrandParent2	StartDate_Grandparent_2	Owner_15	Owner_1	EndDate_Child-1_Of_Parent-3_OfGrandParent2	EndDate_Grandparent_2

[kaushiknsolanki · Accepted solution]

Hi,

Find the solution attached with this post.

Hope this is what you need.

Regards,

Kaushik Solanki

[qvqfqlik]

thankyou very much for your help!