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:
Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName, Activity_Name FROM X where isnull(PARENT_ID);
Load PARENT_ID, Parent_ProjectName, Child_ID, Child_ProjectName FROM X where exists (PARENT_ID,Child_ID);
Load Child_ID as PARENT_ID, Activity_Name FROM X where isnull(PARENT_ID);
Load * ChildrenRows;
Drop table ChildrenRows;
For more levels, you have to use a for loop, making it a bit more complex.
In the script you could also use a PREVIOUS or PEEK funtion. For example:
IF(PEEK('Parent_ProjectName,-1) = Parent_ProjectName , PEEK('Activity_Name_new',-1), Activity_Name) AS Activity_Name_new
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.
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
QlikCommunity_Thread_282859.qvw 161.8 K