3 Replies Latest reply: Nov 25, 2017 5:56 PM by Marco Wedel RSS

    Filling up values from Parent to Child records

    Madhumitha C

      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
        • Re: Filling up values from Parent to Child records
          I. Sorin Rusu

          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.

          • Re: Filling up values from Parent to Child records
            A.M. van Keep

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

              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