4 Replies Latest reply: Jul 4, 2018 7:02 AM by Глеб Аитов RSS

    Renaming values in a field in script

    Глеб Аитов

      Hi!

       

      I have a resulting Maintable with the following fields:

      Channel

      Source

      Visits

       

      I want to run a final cycle where i want to replace certain text information inside the Source field

       

      Noconcatenate //replacing the original Maintable with new one

      Maintable:

       

      Load

       

      Channel, Visits, // ho i have to repeat the names of all existing fields here? My actual field list is huge (if i need all of them, where can i get them as a list of values?

       

      if (Source contains "facebook", 'facebook',

           if (Source contains "instagram", 'instagram', Source) ) as Source // This is what I am trying to achieve, what is the correct syntax?

       

      Resident Maintable;

       

       

      Thank you!

        • Re: Renaming values in a field in script
          Michele De Nardi

          Hi,

          you don't need to repeat all fields during resident load.

          The best way to achieve what you want is to create a mapping table with the source string and the new string:

           

          ReplaceTextTable:
          Mapping
          Load
              SourceString,
              TargetString
          Inline [
          SourceString, TargetString
          facebook, Facebook
          face book, Facebook
          instagram, Instragram
          ];
          
          Rename field Source as Source_TMP;
          Rename Table Maintable to Maintable_TMP;
          
          Maintable:
          NoConcatenate
          Load *,
              Applymap('ReplaceTextTable',Source_TMP,Source_TMP) as Source
          Resident Maintable_TMP;
          Drop Table Maintable_TMP;
          
          Drop field Source_TMP;
          
          
          
          • Re: Renaming values in a field in script
            Christophe Brault

            Hi,

             

            Using preceding load is a good pratice to achieve this kind of things. You can do someting like :

             

            Maintable:

            LOAD *,

                      If(SubStringCount(lower(Source_old),'facebook')=1,'facebook',

                           If(SubStringCount(lower(Source_old),'instagram')=1,'instagram',Source_old)) as Source

            ;

            LOAD Channel,

                      Source as Source_old,

                      Visits,

                      other fields ...

            FROM YourSourceFile ;

             

            DROP FIELD Source_old;

             

            Edit : You can also use wildmatch(Source_old,'*facebook*')=1 instead of the SubStringCount function