PadLeft not working in expression
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all,
I am currently attempting to clean some phone numbers in Alteryx using the Formula tool. One aspect of the cleaning involves finding numbers that start with "614" then trimming the "61" and then adding a "0" at the start of the number, so instead of numbers beginning with "614" they should start with "04". My expression in the Formula tool is as follows:
IF Length([mobile_nm])=11 AND
StartsWith([mobile_nm],"614") THEN
TrimLeft([mobile_nm],"61") AND
PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF
The issue is that the PadLeft function in this expression is not working correctly. What I end up with is a bunch of numbers that are "0" but what I am expecting to see is something like "04XXXXXXXX" (original form would be "614XXXXXXXX").
I have figured out how to fix this by creating a second expression after the first one that is as follows:
IF Length([mobile_nm])=9 AND
StartsWith([mobile_nm],"4") THEN
PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF
But I would prefer to keep it all in one expression. Is there an issue with my syntax in the first expression?
Thanks for any advice.
Expression that doesn't work properly:
Expressions that work properly but I would like to use one expression instead of two:
- Labels:
- Expression
- Help
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The problem is with trying to use the "AND" expression for the true part of your if statement. You can only use AND within the condition part.
IF Length([mobile_nm])=11 AND
StartsWith([mobile_nm],"614") THEN
TrimLeft([mobile_nm],"61") AND
PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF
You have to do it like this:
IF Length([mobile_nm])=11 AND StartsWith([mobile_nm],"614") THEN
Padleft(TrimLeft([mobile_nm],"61"),10,"0")
ELSE
[mobile_nm] ENDIF
