Hello, How can i add comma, bracket (only for negative data) and two decimal number into my output data. I tried multi Row formula tool but issue is these output data stored as text Current Data Product Q1Q2Q3Q4Q5A 4579856458 -45896-8793-8950B-2589-5896457851489-12508C-8963545899856-8934539572 Expected Data Product Q1Q2Q3Q4Q5A 45,798.0056,458.00 (45,896.00)(8,793.00)(8,950.00)B(2,589.00)(5,896.00)4,578.0051,489.00(12,508.00)C(89,635.00)4,589.009,856.00(89,345.00)39,572.00

adding comma and bracket in Data

Hello,

How can i add comma, bracket (only for negative data) and two decimal number into my output data. I tried multi Row formula tool but issue is these output data stored as text

Current Data

Product	Q1	Q2	Q3	Q4	Q5
A	45798	56458	-45896	-8793	-8950
B	-2589	-5896	4578	51489	-12508
C	-89635	4589	9856	-89345	39572

Expected Data

Product	Q1	Q2	Q3	Q4	Q5
A	45,798.00	56,458.00	(45,896.00)	(8,793.00)	(8,950.00)
B	(2,589.00)	(5,896.00)	4,578.00	51,489.00	(12,508.00)
C	(89,635.00)	4,589.00	9,856.00	(89,345.00)	39,572.00

Developer

Developer Tools

Accepted answers

Raj

@kauser @Qiu something comparatively easy to understand 😅

if [_CurrentField_]<0 then Replace("("+ToString([_CurrentField_],2,1)+")","-","") else ToString([_CurrentField_],2,1) endif

hope this helps..

978.yxmd

All comments

cjaneczko

If you add the commas and parenthesis you will not be able to do any calculations with this data in Excel after its exported. If you plan to do any mathematical functions after the export the best bet is to export the file to Excel and then perform the formatting in Excel. Save that file and on the next run of the workflow check the "Preserve Formatting" box in the output tool. If you add the commas and parenthesis in Alteryx its going to treat the field as a String not a Number.

kauser

Yes that's why i am trying to format data at last point just before output tool. i want to format data with comma, decimal and bracket just before transfer to output tool. Also i didn't get your "Peserve Formatting" function.

Qiu

@kauser
Although we can choose the Thousand Seperator in the User settings, but it will not be visible in the result grid.
So I came up with the following by invovling some brutal force. 😂
and referred this thread.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Formula-Tool/m-p/759449#M183229

0404-kauser.yxmd

kauser

@Qiu Thanks for your suggestion but if any cell have more than 6 digits then i am having output like this below-

1. no comma after 6 digits (Second column)

2. comma after negative sign (Third column)

Output Data

A	8643,245,798	-,456,458
B	-89254,672,589	-,325,896
C	-4637,889,635	-,194,589

Input Data

Product	Q1	Q2
A	8643245798	-456458
B	-89254672589	-325896
C	-4637889635	-194589

Raj

978.yxmd

Raj

@kauser please mark done if this solves your problem.

Raj

@kauser

Small change in formula which is edited in replay
if [_CurrentField_]<0 then Replace("("+ToString([_CurrentField_],2,1)+")","-","") else ToString([_CurrentField_],2,1) endif

Qiu

@Raj
Thank you very much for letting know this feature.

😁

2024-04-05 082035.png

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703