1 Reply Latest reply: Oct 5, 2017 3:32 PM by Settu Periyasamy RSS

    Merging Multiple Rows into Single Row as Input

    SENTHIL KUMAR

      Hi Team,

       

      Please see the below Sample source and Expected Output

       

      The Values under Column Value should be taken as New Field as shown below.

         

      Source Table
      TemplateRequest DatePACPROJECTASSETRow NumberColumn NumberColumn TypeMaster TableColumn Value
      BOX18/10/2017ShellPRJ1ASSET111TEXT Topside
      BOX18/10/2017ShellPRJ1ASSET112TEXT Feed
      BOX18/10/2017ShellPRJ1ASSET113TEXT null
      BOX18/10/2017ShellPRJ1ASSET114TEXT DESC 123
      BOX18/10/2017ShellPRJ1ASSET115Integer 11
      BOX18/10/2017ShellPRJ1ASSET116Integer 1
      BOX18/10/2017ShellPRJ1ASSET117Decimal 1244
      Expected Output
      TemplateRequest DatePACPROJECTASSETFacilityItem TypeSub Item TypeDescNo of UnitsUnitsCostPerUnit
      BOX18/10/2017ShellPRJ1ASSET1TopsideFeednullDESC 1231111244
        • Re: Merging Multiple Rows into Single Row as Input
          Settu Periyasamy

          Hi Senthil,

           

          How do you identify the field name for the Column values? Is that static One (Always 7)

          If it is static, You can just read one by one column number with the filter and do the left join.

          if it is a dynamic, may be you can try with Generic Load.

           

          Check this blog : The Generic Load

           

          Directory;
          T1:
          Generic LOAD emplate, 
               [Request Date], 
               PAC, 
               PROJECT, 
               ASSET, 
               'Field'&[Column Number] as Rec,
               [Column Value]
          FROM
          temp_comm\Book1.xlsx
          (ooxml, embedded labels, table is Sheet1);
          
          
          Combined:
          Load distinct emplate FROM
          temp_comm\Book1.xlsx
          (ooxml, embedded labels, table is Sheet1);
            
          FOR i = NoOfTables()-1 to 0 STEP -1
            LET vTable=TableName($(i));
            IF WildMatch('$(vTable)', 'T1.*') THEN
            LEFT JOIN ([Combined]) LOAD * RESIDENT [$(vTable)];
            DROP TABLE [$(vTable)];
            ENDIF
          NEXT i
          
          
          //Field Name Changes
          
          
          NoConcatenate
          Summary:
          LOAD emplate, 
               [Request Date], 
               PAC, 
               PROJECT, 
               ASSET, 
               Field1 as Facility,
               Field2 as [Item Type],
               Field3 as [Sub Item Type],
               Field4 as [Desc],
               Field5 as [No. of Units],
               Field6 as [Units],
               Field7 as [CostPerUnit]
          Resident Combined;
          
          
          DROP Table Combined;
          

           

           

          OutPut:


          Capture.JPG