Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
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