Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
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.
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