Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Make a string type appear as a monetary number without changing datatype

Gina2021
8 - Asteroid

Hello Community!

 

This is my first time uploading a workflow question; so please lmk if I could do it better.

 

Example: column YTD contains string values of 1982577, 886172, 11% that I'm trying to make appear as $1,982,577, $886,172, 11%.  I'll need to convert columns 1-11 like this as well.

 

I've been trying out combinations of Right() and RegEx, but need to learn these skills.  Any advice?

 

Gina2021_0-1619028663701.png

 

 

Thanks so much for your help!

 

Gina

5 REPLIES 5
L_T
8 - Asteroid

Hi Gina,

 

could you please have a look at the attached workflow?

 

L_T_0-1619030197629.png

 

Gina2021
8 - Asteroid

Thanks so much, @L_T ! It is soooo close! I've tried playing with it to keep the [Null] rows [Null] instead of changing them to $0, but just made it worse.  Do you have any tweak ideas by chance? Thanks again for your help!

L_T
8 - Asteroid

 @Gina2021,

 

I have slightly modified the formula in the attached - let me know if this works!

 

T_Willins
14 - Magnetar
14 - Magnetar

Hi @Gina2021,

 

The issue with the Nulls is your sample data has the null cells as text [NULL] and are not truly null values.  If this is consistent with your actual data set, you can update the Multi-Field tool formula to:

 

IF IsNull([_CurrentField_]) OR [_CurrentField_] = "[Null]" THEN "" ELSEIF Contains(ToString([_CurrentField_]),"%") THEN [_CurrentField_] ELSE "$"+ToString(ToNumber([_CurrentField_]),0,1) ENDIF

Gina2021
8 - Asteroid

Thank you tons for the two quick replies @L_T !!! This absolutely works in the example workflow, but when I pull the tool over to my main workflow, I get the $0 in the nulls row.  Thanks tons; you made more sense of this than I did in 4hrs of spinning down a rabbit hole.  SOOO much appreciated!

 

 

 @T_Willins modification to @L_T answer worked when I pulled over to my main workflow.  Thanks so much for calling out the hardcoded "[Null]" data in my rows! That made all the difference!

Labels
Top Solution Authors