3 Replies Latest reply: Feb 27, 2013 1:11 PM by Ashwin Rego RSS

    Combine two records into one

    Ashwin Rego

      Hi,

       

      I have two records coming from the source system because one text field column is very large…see attached.  The user only wants to see one record instead of two. The data from the source system cannot be changed. Is there a way I can do this in the data model or UI using a concatenate function or similar?

       

      Thanks for the help!

        • Re: Combine two records into one
          Gysbert Wassenaar

          You can do it both in the UI and in the script using the concat function. In the script you need to include the other fields in a group by clause:

           

          Data:

          First 1

          LOAD No.,

               [Date First Received],

               [Address 1],

               [Address 2],

               [Address 3],

               CITY,

               State,

               Zip,

               Country,

               concat([Company Narrative],' ') as [Company Narrative]

          FROM

          TB22_20130227_103839.xls

          (biff, embedded labels, header is 1 lines, table is [Sheet1$])

          group by No., [Date First Received], [Address 1], [Address 2], [Address 3],

                    CITY, State, Zip, Country ;

           

          In the UI you can use concat in an expression: concat([Company Narrative], ' ')

          • Re: Combine two records into one
            Michael Solomovich

            Ashwin,

             

            You can concatenate two or more records into one on load from the first table:

            Load

            No,

            "Date First Recieived",

            ...

            concat("Company Narrative", '') as "Company Narrative"

            From ...

            GROUP BY

            No,

            "Date First Recieived",

            ...

             

            Regards,

            Michael

            • Re: Combine two records into one
              Ashwin Rego

              Thanks, both were helpful!