Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Contributor II

trimming and formatting of data in Script

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

1 Solution

Accepted Solutions
rahulpawarb
Valued Contributor III

Re: trimming and formatting of data in Script

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

4 Replies
rahulpawarb
Valued Contributor III

Re: trimming and formatting of data in Script

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
New Contributor II

Re: trimming and formatting of data in Script

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
Contributor II

Re: trimming and formatting of data in Script

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
Valued Contributor III

Re: trimming and formatting of data in Script

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