Filling up values from Parent to Child records

[madhumitha]

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

[isorinrusu · Accepted solution]

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]

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.

[MarcoWedel]

Hi,

one solution might be a Hierarchy‌ load:

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