4 Replies Latest reply: Mar 13, 2017 5:27 AM by Rahul Pawar RSS

    trimming and formatting of data in Script

    Supriya R

      Hi,

      i have below script in load editor, where i need to trim and modify data in `campaign_name` column.

       

      LOAD `campaign_id`,

           `campaign_name`;

      SQL SELECT `campaign_id`,

           `campaign_name`

      FROM CAMPAIGN;

       

      I have like this data in campaign_name column, here usually tha first date will be startdate and second date will be end date

      campaign_name

      Parlegy ab and cd_02-14-2017_02-26-2017

      Britania cd and ab_02-14-2017_02-26-2017

      Britania ab and cd_02-21-2017_03-05-2017

      Parlegy ef and cd_02-21-2017_02-26-2017

      Parlegy de and cd_02-14-2017_02-26-2017

       

      i want to modify like below with this format.

      campaign_name

      Campaign 2/14 - 2/26

      Campaign 2/14 - 2/26

      Campaign 2/21 - 3/05

      Campaign 2/21 - 2/26

      Campaign 2/14 - 2/26

        • Re: trimming and formatting of data in Script
          Rahul Pawar

          Hello Supriya,

           

          Trust that you are doing well!

           

          As per my primary analysis I have come up with draft version of script (It can be fine tuned ;-) ):

           

          CampaignData:
          LOAD campaign_name,
          'Campaign '
          & Replace(Left(RIGHT(campaign_name, 21),5), '-', '/') 
          & '-' & Replace(Left(RIGHT(campaign_name, 10),5), '-', '/') As Newcampaign_name;
          LOAD * INLINE [
          campaign_name
          Parlegy ab and cd_02-14-2017_02-26-2017
          Britania cd and ab_02-14-2017_02-26-2017
          Britania ab and cd_02-21-2017_03-05-2017
          Parlegy ef and cd_02-21-2017_02-26-2017
          Parlegy de and cd_02-14-2017_02-26-2017
          ];
          

           

          Hope this will be helpful.

           

          Regards!

          Rahul

            • Re: trimming and formatting of data in Script
              Abhishek Singla

              Hi,

               

              You can also do this using 'Subfield' and 'Date' function. And you can define any formatting, just need to change 'M/DD' in below code.

               

              CampaignData: 

              LOAD campaign_name, 

              'Campaign ' 

              & Date(Date#(Subfield(campaign_name,'_',2),'MM-DD-YYYY'),'M/DD')

              & ' - '

              & Date(Date#(Subfield(campaign_name,'_',3),'MM-DD-YYYY'),'M/DD')  AS Newcampaign_name;

               

               

              LOAD * INLINE [ 

              campaign_name 

              Parlegy ab and cd_02-14-2017_02-26-2017 

              Britania cd and ab_02-14-2017_02-26-2017 

              Britania ab and cd_02-21-2017_03-05-2017 

              Parlegy ef and cd_02-21-2017_02-26-2017 

              Parlegy de and cd_02-14-2017_02-26-2017 

              ];

               

              Regards!

              Abhishek

              • Re: trimming and formatting of data in Script
                Supriya R

                Hi,

                 

                i have one more thing here

                 

                if campaign name contains New or existing i need to append campaign with New and existing. for example

                 

                campaign_name

                Parlegy ab and cd_02-14-2017_02-26-2017

                Existing Britania cd and ab_02-14-2017_02-26-2017

                Britania ab and cd_02-21-2017_03-05-2017

                New Parlegy ef and cd_02-21-2017_02-26-2017

                Parlegy de and cd_02-14-2017_02-26-2017

                 

                i want to modify like below with this format.

                campaign_name

                Campaign 2/14 - 2/26

                Existing Campaign 2/14 - 2/26

                Campaign 2/21 - 3/05

                New Campaign 2/21 - 2/26

                Campaign 2/14 - 2/26

                 

                How could i do this?

                  • Re: trimming and formatting of data in Script
                    Rahul Pawar

                    Hello Supriya,

                     

                    Please refer below given updated version of script (written by considering New or Existing will be the first words):

                     

                    CampaignData:  
                    LOAD campaign_name,
                    If(SubField(campaign_name, ' ', 1) = 'Existing' OR SubField(campaign_name, ' ', 1) = 'New', SubField(campaign_name, ' ', 1))
                    & ' Campaign '  
                    & Replace(Left(RIGHT(campaign_name, 21),5), '-', '/')   
                    & '-' & Replace(Left(RIGHT(campaign_name, 10),5), '-', '/') As Newcampaign_name;
                    LOAD * INLINE [
                    campaign_name
                    Parlegy ab and cd_02-14-2017_02-26-2017
                    Existing Britania cd and ab_02-14-2017_02-26-2017
                    Britania ab and cd_02-21-2017_03-05-2017
                    New Parlegy ef and cd_02-21-2017_02-26-2017
                    Parlegy de and cd_02-14-2017_02-26-2017
                    ];
                    

                     

                    Also refer the sample application attached herewith.

                     

                    Regards!

                    Rahul