3 Replies Latest reply: May 25, 2011 3:03 PM by Erich Shiino RSS

    Replace existing values with New

      Hi All,

       

      Need your help guys!! :-)

       

      I have attached a sample Excel File & find below the Requirement.

       

      First Sheet(R11) has 3 Rows & second Sheet(R12) has 2 Rows.

      Now i want to replace the whole values of 2nd & 3rd Row from First table(i.e Sheet - R11) with 1st & 2nd Row of the Second Sheet (R12)

      The Key between 2 Sheets/Table is No & New No in 2nd Sheet.

       

      Thanks in Advance :-)

       

      Srini

        • Re: Replace existing values with New
          Erich Shiino

          Im not sure if I understood the link between the tables.

          My basic idea was:

          - Read first the 2nd table.

          - Then, I read from first table only the IDs that I didn't have on 2nd table.

           

          You probably are going to rename some field to actually replace values, but I was not sure which ones you needed.

           

          Hope it helps,

           

          Erich

           

           

          Table:
          LOAD ID,
               Num,
               [New No],
               Amt
          FROM
          [Incremental Testing Logic.xlsx]
          (ooxml, embedded labels, table is R12);
          Concatenate(Table)
          LOAD ID,
               Num,
               Revenue
          FROM
          [Incremental Testing Logic.xlsx]
          (ooxml, embedded labels, table is R11)
          where not exists(ID)
          ;
          
          
            • Re: Replace existing values with New

              Hi Erich,

               

              My Requirement is as below

              First Sheet (R11) contains 3 Rows

              Second Shet(R12) contain 2 Rows

               

              Now the Link between the First sheet & second shett is the No (Field from first sheet) & Num (Field in the second Sheet).

               

              Now i want a report as attached File (As given in Sheet 3).

              Now one more thing if in case i keep on adding any new rows in the R12(sheet2) then these rows should keep on appending in the Report.

               

              Please let me know if you need any further info on the same

               

              Thanks a lot for your kind help on the same. :-)

               

              Srini

                • Re: Replace existing values with New
                  Erich Shiino

                  Hi, The script is  almost the same

                  I just changed some field names to match the requirement.

                  The logic is.

                  Read all sheet 2 (since if there is the same 'Num' in sheet1 it would be replaced anyway)

                  Read from sheet1 only the records with  'Num' that was NOT previously loaded (using not exists)

                   

                  Hope it helps,

                   

                  Erich

                   

                   

                  Table:
                  LOAD ID,
                       Num,
                       [New No],
                       Amt
                  FROM
                  [Incremental Testing Logic.xlsx]
                  (ooxml, embedded labels, table is R12);
                  Concatenate(Table)
                  LOAD ID,
                       Num,
                       Revenue as Amt
                  FROM
                  [Incremental Testing Logic.xlsx]
                  (ooxml, embedded labels, table is R11)
                  where not exists(Num)
                  ;