Cleaning strings in Alteryx TipTuesday #TipWeek

by Benedetta Tagliaferri

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:

  1.  AW033_nameOfTheCampaign
  2. OBC436a nameOfTheCampaign

In order to have my ‘nameOfTheCampaign’ nice and clean in my field I had to make some transformations with the tool formula.

tuesday1

 

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.

 

tuesday2

 

I hope this is useful!

See you tomorrow!

 

Avatar

Benedetta Tagliaferri

Machine Learning 101: the Cost Function or Squared Error Function

2 mins read

Sun 06 Mar 2016

How to add an Academy Award to an Oscar-nominated Film

1 mins read

Thu 03 Mar 2016

Machine learning 101: a really simple explanation about Linear Regression

3 mins read

Thu 25 Feb 2016

How to download Json files in Alteryx Tip Thursday #TipWeek

1 mins read

Thu 18 Feb 2016