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

Alter Data formula help

Brad1
11 - Bolide

How do I convert:

 

A03   to   1103

B09   to   1209

 

(A means 11, B means 12)

 

Thanks in advance.

7 REPLIES 7
Kenda
16 - Nebula
16 - Nebula

Hey @Brad1! I would suggest using the following expression in a Formula tool:

 

REGEX_Replace(REGEX_Replace([Field1], "A", "11"),"B","12")

Hope this helps!

Brad1
11 - Bolide

My formula is not like yours.  I think I like yours better.  Mine is running now.  Waiting to see what comes out.  I don't think mine will address if more than two digits after the "A".  Looks like yours will.

Brad1
11 - Bolide

Or maybe it won't.

Brad1
11 - Bolide

I should have mentioned the last two digits could be any two digits.  Here's what I wrote and it works.

 

IF(Left([CHAIN], 1) = 'A') THEN '11' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'B') THEN '12' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'C') THEN '13' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'D') THEN '14' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'E') THEN '15' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'F') THEN '16' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'G') THEN '17' ELSE [CHAIN] ENDIF

Brad1
11 - Bolide

Yeah, yours is better.  Thanks!!!

Kenda
16 - Nebula
16 - Nebula

@Brad1 Because you are checking more than just A and B, you could consider using a Find and Replace tool instead. This may be less messy and could be easier to update if you need to in the future. In the F anchor, you will feed in your initial field (I named mine find). On the R side, you will feed in a Text Box that you create that has one column with the string you want to find (here, A, B, C, etc.) and one column with the replacement value (11, 12, etc.) as a string. This will output just the find field with the replacements that took place. Either this way or the formula before should work!

 

findreplace.PNG

patrick_digan
17 - Castor
17 - Castor

@Brad1 You may be able to use the find replace tool as well. I've attached the start of what that would look like.

 

Just a thought!

 

EDIT: I see @Kenda and I are on to the same idea.

Labels