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

Formatting a 'filter' for if>then>else

Archaeopteryx
10 - Fireball

Hi,

 

I'm attempting to use a formula filter that will prepend a character to a value if the value meets a condition.

 

Here is what I have:

 

StartsWith([ID],"8",Replace([ID], "8", "'8"))

 

So if [ID] starts with the character 8, I want to prepend a single apostrophe to it.

But when I run the job, my spreadsheet gets wiped. I had data before I inserted this formula, so I believe this formula to be the culprit.

 

Can someone tell me what I'm doing wrong?

 

Thanks,

1 REPLY 1
jrgo
14 - Magnetar

Hi @Archaeopteryx

 

Yes, there is an issue with your expression. The "StartsWith" function returns a logical value, or TRUE/FALSE. The first two parameters in your expression are correct, but the third is not. You're using it by nesting the "Replace" function, however, that argument is used to specify whether the lookup is case sensitive or not (default is TRUE (which is an optional parameter)). 

StartsWith(String, Target, CaseInsensitive=1)

Try this expression and see if it gets you the output you're looking for.

IF StartsWith([ID],"8")
THEN ReplaceFirst([ID], "8", "'8")
ELSE [ID]
ENDIF

I did use a different replace function as the one you were attempting to use would have replaced all "8" characters in the string, not just the first one.

 

Hope this helps!

 

Best,

 

Jimmy

Labels