Welcome in Tip Tuesday of DS2 for our #TipWeek!
Today I am going to speak about strings in Alteryx. In particular this tip will help you to clean string fields in your dataset.
Last week we had a client project where I had to create an automated evaluation process for marketing campaigns. As a campaign label I had this to type of strings:
- AW033_nameOfTheCampaign
- OBC436a – nameOfTheCampaign
In order to have my ‘nameOfTheCampaign’ nice and clean in my field I had to make some transformations with the tool formula.
I had to create an IF THEN ELSE conditional formula. If the number of letters from the left would have been the same for each row, then I would have used the ‘TrimLeft(string,len)’ formula inside stings functions.
After many attempts I solved my problem with this formula:
IF Contains([delivery_label], ‘_’)
THEN
RIGHT([delivery_label],
(Length([delivery_label])-
(FindString([delivery_label], ‘_’)+1))
)
ELSE
RIGHT([delivery_label],
(Length([delivery_label])-
(FindString([delivery_label], ‘ – ‘)+3))
)
ENDIF
I had some campaigns with an _ as a separation between the code and the name of campaigns and others with SPACE-SPACE ( – ).
So I wrote: IF contains inside delivery label (name of the campaigns) this symbol ‘_’, then take the right part of the string. In order to specify the length that I want to take from the right (and because the length of the rows are all different, I wrote length of delivery label minus the code part (FindString of delivery_label at this point ‘_’ and add 1 so it doesn’t take the _ character).
The formula continues with ELSE, but in this case we want SPACE-SPACE ( – ) so I wrote +3 because we want to get rid of two spaces plus one character.
I hope this is useful!
See you tomorrow!