trimming and formatting of data in Script

[berryandcherry6]

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

[rahulpawarb · Accepted solution]

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

[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

[berryandcherry6]

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?

[rahulpawarb]

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