4 Replies Latest reply: Apr 17, 2017 4:13 PM by Muñoz Héctor RSS

    New table with values calculated from 3 existing tables

    Jonathan Love

      I would like to create a new table that holds values calculated on the contents of three existing tables.  I cannot alter the way the existing tables are joined in any way or the whole thing falls apart

       

      The table should hold the values:

       

      (Parent-Junctions)(Child_Junctions)(Service Groups)
      p_Child_Ofc_Child_OfPSG_Name
      p_Parent_Toc_Parent_To

       

      And the calculation would be something like:

       

      If(c_Child_Of = PSG_Name AND p_Parent_To = '', 'Parent_Only',

           If(c_Child_Of = p_Parent_To, 'Parent_And_Child',

                If(c_Child_Of = '' AND p_Parent_To = PSG_Name, 'Child_Only')))     as SG_Identity

       

       

      Parent_Junctions:

      LOAD

      PSGJ_Name as p_PSGJ_Name,
      PSGJ_Id    as p_PSGJ_Id,
      //     PSGJ_ParentGroupFrom,
           PSGJ_ChildGroupTo,
      [Child Of] as p_Child_Of,
      [Parent To]     as p_Parent_To
      FROM
      [C:\Users\uslovjon\Desktop\Service_Groups\20170413_Groups and Junctions(VLUP).xlsx]
      (
      ooxml, embedded labels, table is Junctions);

      Child_Junctions:

      LOAD

      PSGJ_Name as c_PSGJ_Name,
      PSGJ_Id    as c_PSGJ_Id,
      PSGJ_ParentGroupFrom,
      //     PSGJ_ChildGroupTo,
           [Child Of] as c_Child_Of,
      [Parent To]    as c_Parent_To
      FROM
      [C:\Users\uslovjon\Desktop\Service_Groups\20170413_Groups and Junctions(VLUP).xlsx]
      (
      ooxml, embedded labels, table is Junctions);


      Service_Groups:

      LOAD

      PSG_Id    as PSGJ_ParentGroupFrom,
      PSG_Id    as PSGJ_ChildGroupTo,
      PSG_Id,
      PSG_Name,
      PSG_Create_From_Child,
      PSG_Maximum_Concurrent_IP,
      PSG_Minimum_Concurrent_IP,
      PSG_Business_Responsible
      FROM
      [C:\Users\uslovjon\Desktop\Service_Groups\20170413_Groups and Junctions(VLUP).xlsx]
      (
      ooxml, embedded labels, table is Groups);



      PSG Model.PNG

       

        • Re: New table with values calculated from 3 existing tables
          Muñoz Héctor

          Hi Jonathan,

          A trick you could use is to create mapping tables from Child_Junctions and Parent_Junctions tables:

           

          MAP_01:

          MAPPING LOAD     PSGJ_ParentGroupFrom     AS [MAP_01 PSGJ_ParentGroupFrom],

                                         c_Child_Of                       AS [MAP_01 c_Child_Of]

          RESIDENT              Child_Junctions;

           

          MAP_02:

          MAPPING LOAD     PSGJ_ChildGroupTo          AS [MAP_02 PSGJ_ParentGroupFrom],

                                         p_Parent_To                    AS [MAP_02 c_Child_Of]

          RESIDENT              Parent_Junctions;


          , and loading necessary fields from them into table Service_Groups with the idea if creating a nested LOAD in which you cretaed the necessary calculated field:


          Service_Groups:

          LOAD *,

          If(Service_Groups_c_Child_Of = PSG_Name AND Service_Groups_p_Parent_To = '', 'Parent_Only',

               If(Service_Groups_c_Child_Of = Service_Groups_p_Parent_To , 'Parent_And_Child',

                    If(Service_Groups_c_Child_Of = '' AND Service_Groups_p_Parent_To = PSG_Name, 'Child_Only')))     as SG_Identity;
          LOAD
          PSG_Id    as PSGJ_ParentGroupFrom,
          PSG_Id    as PSGJ_ChildGroupTo,
          PSG_Id,
          PSG_Name,
          PSG_Create_From_Child,
          PSG_Maximum_Concurrent_IP,
          PSG_Minimum_Concurrent_IP,
          PSG_Business_Responsible,

          ApplyMap('MAP_01', PSGJ_ParentGroupFrom, Null())     AS Service_Groups_c_Child_Of,

          ApplyMap('MAP_02', PSGJ_ChildGroupTo, Null())        AS Service_Groups_p_Parent_To

          FROM
          [C:\Users\uslovjon\Desktop\Service_Groups\20170413_Groups and Junctions(VLUP).xlsx]
          (
          ooxml, embedded labels, table is Groups);


          Hope it serves!


          Regards,H