Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Replace function with error coz of the data does not contain

Jwwwson
8 - Asteroid

Hi, 

 

I would like to ask about how should I set up the workflow to replace a string in a column, say from 'TBDstudent1' to 'ABCstudent1' and   'TBDstudent2' to 'ABCstudent2' etc. 

 

I am currently using the 'Replace' function, however, when a batch of data does not contain a certain string 'TBDstudent1', error occurs. How shall I design the formula or utilise the tool that the replace function only be performed when the column contains such specific target, so that I do not need to modify the workflow each time? 

 

Thanks!

 

Cheers,

9 REPLIES 9
grazitti_sapna
17 - Castor

Hi @Jwwwson , you can use if else condition in the formula tool, as there is just one example for replacing TBD with ABC so it works if you have any other conditions to be specified you can modify the formula and use elseif. Please follow the screenshot for clarity.
Let me know if that helps.

Formula- if Contains([Field1],"TBD") then Replace([Field1], "TBD", "ABC") else [Field1] endif

 

grazitti_sapna_0-1591694501391.png

 

 

Sapna Gupta
ChrisTX
15 - Aurora

The REPLACE function itself will not generate an error if the search string is not found.  The error must me somewhere else in your workflow.

 

Which tool is generating the error?  Can you post a copy of your workflow, including sample input data?

 

Chris

Jwwwson
8 - Asteroid

Thanks @grazitti_sapna ! It works! 

 

May I ask one more question, does IF function includes two criteria, one is A column(job name) include a specific wordings and the second criteria is column B Does not contain certain wordings, and if the criteria are fulfilled, the wording of another column (Booking Type_) will be changed, otherwise it will remain as original. The formula is shown below, however, the output is always null, would you please kindly advise if anything goes wrong? Thanks!

 

 

IF Contains([Job name], "BUSINESS DEVELOPMENT") AND !Contains([Manager], "Peter" OR "Amy") then Replace([Booking Type_], "Non A", "A") else [Booking type] endif

 

grazitti_sapna
17 - Castor

Hi @Jwwwson , it is just a minor mistake in the formula you used that is why you are getting null. Yes, you can specify two different conditions in "if" function. Please find the attachment below and let me know if this is what you are looking for.

 

Formula- IF Contains([Job name], "BUSINESS DEVELOPMENT") AND (!Contains([Manager], "Peter") AND !Contains([Manager], "Amy")) then Replace([Booking Type], "Non A", "A") else [Booking type] endif

 

Thanks!

 

grazitti_sapna_0-1591698451278.png

 

 

Sapna Gupta
Jwwwson
8 - Asteroid

Hi @grazitti_sapna

 

Attached please the sample raw data and workflow. Many thanks!

 

 

Cheers, 

grazitti_sapna
17 - Castor

Hi @Jwwwson , I have modified the formula for you and I hope it works as per the requirement. Attaching the screenshot along with the workflow. I hope it helps!

Thanks!

 

Formula- IF Contains([Job name], "BUSINESS DEVELOPMENT") AND (!Contains([Manager], "Peter") AND !Contains([Manager], "Amy")) then Replace([Booking Type], "Non A", "A") else [Booking type] endif

 

grazitti_sapna_0-1591698774239.png

 

Sapna Gupta
ChrisTX
15 - Aurora

With your sample workflow, I'm only seeing a warning, not an error.

 

The warning message is: Warning: Formula (2): Formula: tried to apply string operator to numeric value (Contains) Record #1 in field Booking Type_

 

This warning message is somewhat misleading.

 

The problem seems to be with the structure of the second CONTAINS in your IF function.

 

This syntax is not correct:   !Contains([Manager], "Peter" OR "Amy")

 

Instead you could use:    [Manager] NOT IN ("Peter", "Amy")

 

Chris

 

Jwwwson
8 - Asteroid

@grazitti_sapna, Thanks a lot!

grazitti_sapna
17 - Castor

@Jwwwson welcome.

Sapna Gupta
Labels