Formula Assistance Needed

I'm in the process of transferring over some logic from ACL and have been unable to get the below to translate into Alteryx. The goal is to identify and target our 5 digit string fields and translate them into various 6 digit fields based upon the first number. ex: identify 43210 and translate it to 253210

Below is the logic that was used in ACL. Any assistance or advice would be greatly appreciated. Thanks!

ALLTRIM("25" + SUBSTR(INCLUDE(XXX, "0123456789"),2,4)) IF Length(ALLTRIM(INCLUDE(XXX, "0123456789"))) =5 and SUBSTR(INCLUDE(XXX, "0123456789"),1,1) = "4"

Workflow

Transformation

Help

Accepted answers

BrandonB

What about something like this?

IF Length([XXX]) = 5 AND Left([XXX],1) = "4"

THEN "25"+Right([XXX], 4)

ELSE [XXX]

ENDIF

All comments

BrandonB

What about something like this?

IF Length([XXX]) = 5 AND Left([XXX],1) = "4"

THEN "25"+Right([XXX], 4)

ELSE [XXX]

ENDIF

tonypreece

Hey @tp2

I'm not familiar with ACL, but if I'm reading your question right you simply want to replace the first digit of the string with two other digits?

If so, first, make sure it is stored as a string and not a number, then you can use this formula:

Example: Change from 1 to 10, if the first digit isn't a 1, do nothing.

IIF(left([FIELD],1)=4,25,[FIELD])

Then you can nest the statements:

IIF(left([FIELD],1)=4,25, IIF(left([FIELD],1)=2,20,[FIELD]) and so on.

Claje

Hi,

I'm not familiar with ACL functions/logic at all, so I'm going to attempt to describe what I Think your logic is doing, and then provide some equivalents in Alteryx.

I think this is saying the following:

Remove all non-digit values from the target field, then IF the new string of digits has a length of 5 characters, and the first character is a "4", create a new value that appends "25" to the last 4 characters of your new string.

If this understanding is correct, below is my recommendation:

The easiest way I know of in Alteryx to remove non-digit characters from a string is with Regular Expressions. In the Formula tool, the following should do the same as the "INCLUDE" function in ACL:

REGEX_Replace([YOURFIELDNAME], '[^\d]', '')

This will replace any non-digit (0-9) characters in your field with nothing, leaving us with only digits.

In Alteryx, conditional logic like an IF has to start the formula, so instead of saying "Do this IF {condition}", we will instead say "If {condition}, do this"

The last important note is the Substring function. It looks like from your example the ACL "SUBSTR" function starts counting at 1. The equivalent function in Alteryx is SUBSTRING, and it starts counting from 0. So if we reduce the first number on each SUBSTR function by 1, the rest should work.

As such, I think your final function (in one formula) is:

IF LENGTH(REGEX_Replace([YOURFIELDNAME], '[^\d]', '')) = 5 AND SUBSTRING(REGEX_Replace([YOURFIELDNAME], '[^\d]', ''),0,1)="4" THEN '25'+SUBSTRING(REGEX_Replace([YOURFIELDNAME], '[^\d]', ''),1,4)
ELSE NULL()
ENDIF)

This defaults to no value if we don't find a match. you could change the NULL() to whatever logic you want.

tp2

Thank you all so much, this worked like a charm!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703