2 Replies Latest reply: Dec 2, 2014 3:05 PM by saurabh singh RSS

    trim, subfield issue

    saurabh singh


      hi All,

      i have a field  named cust_name_key, with values as follows:

      Abraham,Janet (A526243)

      Kaur,Jaswin (CX12345)

      Tammy,Silo (C213211)

      lampo,Shawn(lil) Adams(D343432)

       

       

      the requirement is to get to fields name and key

      name                                            key

      Abraham,Janet                         A526243

      Kaur,Jaswin                              CX12345

      Tammy,Silo                               C213211

      Lampo,Shawn (lil)  Adams    D343432

       

      in order to extract name i used

      subfield(cust_name_key,right(cust_name_key,9)) as name,

      it works but couldnt extract key as some names had brackets in them and chr lenght may vary, can sumebody suggest something?

        • Re: trim, subfield issue
          Anand Chouhan

          Hi Saurabh,

           

          Try this way i am loading your table from the excel source file you can load your table because there is comma( , ) in the data.

           

          Try this

           

          Source:

          LOAD

          cust_name_key,

          subfield(cust_name_key,right(cust_name_key,9)) as name,

          PurgeChar(right(cust_name_key,9),'(,)') as Key;

          LOAD cust_name_key

          FROM

          [NameSource.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          NoConcatenate

          Final:

          LOAD * Resident Source Where Len(name)>0;

          DROP Table Source;

           

          subfieldop.png

           

          Regards

          Anand