Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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?
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