changing data type to currency?

[paulold]

I am building an Alteryx workflow that simply helps me to format my monthly data pull. I work with Analysis for Office to pull data from our business system and export the results to Excel. I realized that I do the same set of steps every month to this data so I'm creating a workflow to have Alteryx do it for me. Basically, I am filling in blank headers, renaming some headers, rearranging the columns and adjusting some data types. And I'm stuck on the data type part.

When I import my untouched source file, all of the fields end up with the Type of V_WString in Alteryx. This is fine for the majority of the fields. But I have some fields that contain currency. When I export the data from AO to Excel, Excel recognizes these fields as numbers without any formatting. Part of my monthly process was to highlight these columns in Excel and change the data type to Currency, using the default settings of $#,###.##. But with Alteryx, when I import the untouched file, all of the fields end up as V_WString and while I can change the Type, I don't see a Currency option. Looks like the best I can do is change the Type so that it's a number, by using say Double. Is that my only choice? Again, the whole point of creating this workflow is to automate all of the steps I take each month. It's seems odd to me that Alteryx can do almost everything I do each month except changing the Type to Currency. Is there a way?

Also, is Double the best choice for changing the data type from text to number?  

For extra credit: We actually have US Dollars, Euros and British Pounds in our data. I usually just convert to Currency in Excel, giving everything dollar signs. But if I'm taking the time to create this workflow, maybe I should have the data be more accurate by displaying the correct currency symbol. That may be taking things too far, but I thought I would mention it. We do have a separate field that identifies the currency type (USD, EUR, GBP) which I suppose could be used in a formula.

[RogerS]

You will need to convert it to a number if you need to do in math operations.  You can then either use the create table tool (render for output) if you want to flip it back to currency.  You can also output to an excel range and keep the formatting so you do no have to manually change it. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-output-to-formatted-sheet/td-p/5...

[danilang]

Hi @paulold 

The closest native type to Currency is Fixed Decimal.  Use a Select tool to convert it and change the default length to 19.2, giving you 2 places after the decimal.  

To add the Currency symbol, use a formula like [Symbol]+tostring([Amount],2,",")  to format your amount with 2 decimal places and comma as the thousands separator 

Note that when you output this to excel, the amounts will be seen as string fields because of the included symbols.  To get around this, you can try outputting the raw amount to a named range in an existing spreadsheet.  This will allow you to use the Preserve Formatting on Overwrite option in the Output tool

Dan