remove negative sign and replace parenthesis with 2 decimals

[pdhanase]

workflow has been developed with macro to split the city into different files. i need support to convert the output files values into which is now in negative sign to Parenthesis (within brackets) with decimals.

This should be -208.4519102 changed to (208.45). Also request to not suggest to create any new columns, which will disturb the workflow run, as we will remove the completed month for every month run.

Herewith attached the workflow and macro along with linked two excel file - one with data, second to use for split into different files.

[MarqueeCrew]

Hello @pdhanase ,

I'd use a Multi-Field Formula tool and create the transformation from a NUMERIC to a V_STRING for your amount fields.  Please remember that these field conversions will make them strings and that there will be conversion warnings.

Select your NUMERIC fields (any/all that you want to change)

Uncheck the "copy output ..." option

Check the Change Output Type to V_String Size 64

Expression:

IIF([_CurrentField_]<0,"(" + ToString([_CurrentField_],2,1) + ")",ToString([_CurrentField_],2,1))

I added commas for thousands... If you don't want the commas, change both of the ToString() functions to ,2,0

Cheers,

Mark

[Luke_C · Accepted solution]

To add on to @MarqueeCrew 's solution, I don't think you even need the macro in the workflow. From what I can tell it's being used to filter on each city and render the reports separately, which can just be done using the grouping options in the reporting tools themselves. 

[pdhanase]

Yes Luke. Much appreciate with your workflow. it works without Macro.

Two changes required in the output file

Negative numbers are in bracket, but coming as (-2.48) instead of (2.48) and

positive number are in text string, should be numbervalue with 2 decimal. (Screenshot attached to remove the red highlight rounded)

Thanks

[Luke_C · Accepted solution]

Hi @pdhanase 

Update the formula in the tool to:

IIF([_CurrentField_]<0,"(" + replace(ToString([_CurrentField_],2,1),"-","") + ")",ToString([_CurrentField_],2,1))

For the formatting, not much I can do about that. This specific format you require means that we need to convert the fields to be strings in alteryx, so they are output as strings (text) in excel. You might consider not changing the format in alteryx and instead handling the formatting in excel. 

[pdhanase]

Thanks Luke. This now works as you said and i will format text string in excel. Is it possible to draft an email for city wise file separately in outlook from Render (may be with same body message with different recipient for each file)