3 Replies Latest reply: Nov 22, 2015 9:25 PM by Annette Gordon RSS

    Help with field concatenation during load


      Hi,

       

      I have a table with sample data as follows:

                                                                  

      OPT_VGUIDCHARCVLC_VALUE
      ASD_D000006023
      ASD_D050002828
      ASD_M000000090
      BSD_D700002309
      BSD_D710003823
      BSD_J350006634
      BSD_JAA0002647
      BSD_JDN0006695
      BSD_JER2001484
      BSD_LNA0014632

       

       

      during the load I need the VLC_VALUE field to concatenate based on each unique OPT_VGUID, so the result is:

                     

      OPT_VGUIDVLC_VALUE
      A06023 - 02828 - 00090
      B02309 - 03823 - 06634 - 02647 - 06695 - 01484 - 14632

       

      is this possible?

        • Re: Help with field concatenation during load
          Sunny Talwar

          Try this:

           

          Table:

          LOAD OPT_VGUID,

                    CHARC,

                    VLC_VALUE,

                    RowNo() as Sort

          FROM Source;

           

          NewTable:

          LOAD OPT_VGUID

                    Concat(VLC_VALUE, ' - ', Sort) as VLC_VALUE

          Resident Table

          Group By OPT_VGUID;

           

          DROP Table Table;

          • Re: Help with field concatenation during load

            Thanks Sunny

            I found this wasn't working with the 'Sort' concatenation as you have provided.

             

            so I amended as follows - 

            TABLE_1:
            LOAD  OPT_VGUID,

                    CHARC,
                 
            VLC_VALUE,
                 
            RowNo() as OPT_Sort
            FROM
            SOURCE;

            NEW_TABLE:
            LOAD OPT_VGUID,
                
            Concat(VLC_VALUE, ' - ') as NEW_VLC_VALUE
            Resident TABLE
            group by VGUID;

            DROP table TABLE_1;

             

            This gave the required results and works perfectly!

                                     

            OPT_VGUIDNEW_VLC_VALUE
            A02309 - 02647 - 03823
              - 78817
            B00131 - 00632 - 01470
              - 01526 - 02116 - 02415 - 02817 - 06312 - 08297
            C00131 - 00632 - 01074
              - 01470 - 01526 - 01557 - 02415 - 02817 - 06312
            D00131 - 00632 - 01470
              - 01526 - 02116 - 02415 - 02817 - 06312 - 06546 - 08297

             

             

            thanks for your help!

            • Re: Help with field concatenation during load
              jagan mohan rao appala

              Hi,

               

              Try like this

               

              Data:

              LOAD OPT_VGUID

                        Concat(VLC_VALUE, ' - ') AS VLC_VALUE

              GROUP BYOPT_VGUID;

              LOAD

              *

              FROM DataSource;

               

              Hope this helps you.

               

              Regards,

              Jagan.