Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

madhumitha
Contributor

Filling up values from Parent to Child records

Hello all,

I have below data in my resident table.

I just have to fill in the activity name in all the child records, fetching data from corresponding parent.

please note, for the Master record, the Parent ID will be null and the Child ID alone will have ID.

Completely blank on the ways. Could anyone please help?

   

PARENT IDParent_ProjectNameChild IDChild_ProjectNameActivity_Name
AB12345ABProcess Improvement Activities
12345AB23456CA
12345AB23457CA
12345AB23458CA
Tags (1)
1 Solution

Accepted Solutions
isorinrusu
Contributor II

Re: Filling up values from Parent to Child records

Hi Madhumitha,

When I encountered this problem, I've always solved it using left joins. So you'd load the table N times for each level that you have in your hierarchy.

For example, you'd do for the first level:

ParentRow:

Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName, Activity_Name FROM X where isnull(PARENT_ID);

noconcatenate

ChildrenRows:

Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName FROM X where exists (PARENT_ID,Child_ID);


Left Join(ChildrenRows)

Load Child_ID as PARENT_ID, Activity_Name FROM X where isnull(PARENT_ID);


Concatenate (ParentRow)

Load * ChildrenRows;


Drop table ChildrenRows;


For more levels, you have to use a for loop, making it a bit more complex.


Regards,

Sorin.

3 Replies
isorinrusu
Contributor II

Re: Filling up values from Parent to Child records

Hi Madhumitha,

When I encountered this problem, I've always solved it using left joins. So you'd load the table N times for each level that you have in your hierarchy.

For example, you'd do for the first level:

ParentRow:

Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName, Activity_Name FROM X where isnull(PARENT_ID);

noconcatenate

ChildrenRows:

Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName FROM X where exists (PARENT_ID,Child_ID);


Left Join(ChildrenRows)

Load Child_ID as PARENT_ID, Activity_Name FROM X where isnull(PARENT_ID);


Concatenate (ParentRow)

Load * ChildrenRows;


Drop table ChildrenRows;


For more levels, you have to use a for loop, making it a bit more complex.


Regards,

Sorin.

avkeep01
Valued Contributor

Re: Filling up values from Parent to Child records

Hi Madhumitha,

In the script you could also use a PREVIOUS or PEEK funtion. For example:

ParentRow:

Load

PARENT_ID,

Parent_ProjectName,

Child_ID,

Child_ProjectName,

Activity_Name ,

IF(PEEK('Parent_ProjectName,-1) = Parent_ProjectName , PEEK('Activity_Name_new',-1), Activity_Name) AS Activity_Name_new

RESIDENT  previoustablename

ORDER BY Parent_ProjectName,Child_ProjectName;


Sort the data by Parent_ProjectName and Child_ProjectName so you fill the correct values. Then check whether the previous parent_id equals the current parent_id (same for child_projectname btw), if the value isn't the samen then it is the first value so,, just use the activity_name from the current row, otherwise pick the value from the previous row.

Re: Filling up values from Parent to Child records

Hi,

one solution might be a Hierarchy‌ load:

QlikCommunity_Thread_282859_Pic1.JPG

table1:

Hierarchy ([Child ID],[PARENT ID],Activity_Name)

LOAD RecNo() as RecID, *

Inline [

    PARENT ID, Parent_ProjectName, Child ID, Child_ProjectName, Activity_Name

        , AB, 12345, AB, Process Improvement Activities

    12345, AB, 23456, CA

    12345, AB, 23457, CA

    12345, AB, 23458, CA

    23458, CA, 34567, BC

    23458, CA, 34567, BC

        , EF, 56789, EF, Supporting Activities

    56789, EF, 67890, FG

    56789, EF, 67891, FG, Supporting Subactivities

    56789, EF, 67892, FG

    67891, FG, 78901, GH

    67891, FG, 78902, GH  

];

(probably dropping the original Activity_Name field and renaming the Activity_Name1)

hope this helps

regards

Marco

Community Browser