Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Converting suffix number (K M B T) to general number format (long number)

Highlighted
8 - Asteroid

Been looking in the community for an answer to this.  Maybe I'm not putting in the right search terms, so I'll ask the question.

 

If there a way or formula to convert suffixed numbers to general number format?

 

1.2M1200000
1K1000
3.27B3270000000
5T5000000000000
2.2T2200000000000

 

It can be done in excel with this formula:

 

IFNA(LEFT(H28,LEN(H28)-1)*CHOOSE(MATCH(RIGHT(H28,1), {"K","M","B","T"},0),1000,1000000,1000000000,1000000000000),H28)

 

Thanks

 

 

Highlighted
Alteryx
Alteryx

@ibesmond ,

 

you could try this! hope this helps

bpatel_0-1593208096339.png

 

Highlighted
8 - Asteroid

That's a solid solution.  Thank you. Is there a way this could be applied to multiple fields without having to add 5 regex tools, and maybe using a replace tool to cut down on the number of formulas?

 

This was my original solution. Its a long complicated if statement. It works because I know I have at most 1 decimal point.  

 

IF contains([Revenue (USD)],"K") AND !Contains([Revenue (USD)],".")
THEN Replace([Revenue (USD)],"K","000")
ELSEIF contains([Revenue (USD)],"K") AND Contains([Revenue (USD)],".")
THEN (Replace([Revenue (USD)],"K","00") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "M") AND !Contains([Revenue (USD)], "."))
THEN Replace([Revenue (USD)],"M","000000")
ELSEIF (Contains([Revenue (USD)], "M") AND Contains([Revenue (USD)], "."))
THEN (Replace([Revenue (USD)],"M","00000") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "B") AND Contains([Revenue (USD)], "."))
THEN (Replace([Revenue (USD)],"B","00000000") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "B") AND !Contains([Revenue (USD)], "."))
THEN Replace([Revenue (USD)],"B","000000000") ELSE
[Revenue (USD)] ENDIF

 

The formula is large and will break if introduced a value like 1.22M.  The plus side is I can convert all 5 fields in one formula tool.

Highlighted
Alteryx
Alteryx

hi @ibesmond ,

 

you can use the transpose and cross tab to achieve this for multiple fields. for example if this was your starting data

bpatel_0-1593464313339.png

you can build a workflow like this

bpatel_1-1593464335940.png

 

to get this output

 

bpatel_2-1593464361087.png

hope this helps!

 

Labels