How to add rows in column with same numbers but make them negative

Question

Hello,

I have data with two columns that look like the following:

Order IDAmount123456194.28018417480.171047721,294.29

I want to create new rows in the Amount field with the negative balance of the amount for each Order ID, such as the following:

Order IDAmount123456

194.28123456-194.28018417480.17018417-480.171047721,294.29104772-1,294.29

Any ideas for a quick formula to accomplish this? I'm assuming it will happen in a Generate Rows tool, but I think a Multi-Row Formula tool would work as well? Anything helps! Thank you

Luke_C · Accepted Answer

Hi @Smiller100

Try something like this:

Essentially convert the numbers to negative and union back with the original data set. Note: I changed your input data to remove commas so Alteryx would automatically recognize it as numeric.

atcodedog05 · Accepted Answer

Hi @Smiller100

Adding to @Luke_C solution here is another way to do it.

Workflow:

Hope this helps : )

AngelosPachis · Accepted Answer

Hello @Smiller100 ,

Since you want to generate a single new row per record, you can use a generate rows tool and set the number of new rows generated to 2. Then you can use a formula tool and an IF expression, to make the new rows generated a negative amount.

You might want to pay attention to the data type, as your Amount column originally is a string. You can either keep it as is, or convert it to a numeric data type.

Hope that helps,

Angelos