How to add a new column with data from existing column but conditional

[KyraM]

Hi, 

I'm trying to add a new column "Amount USD" to my dataset in Alteryx, which should be conditional to a previous column, but I can't figure out what the exact formula has to be. 

E.g.:

G/L Account	Amount
Trade Sales	100.000,00
Intercompany Sales	100.000,00
Cost of Goods Sold	80.000,00
Distribution Expense	10.000,00

For Trade Sales and Intercompany Sales I want the column "Amount USD"  to display negative amounts of "Amount" and for the other two I want it to show "Amount".

The formula that I tried using the formula tool is as follows (I selected that I want an output column "Amount USD"):

IF [G/L Account]="Trade Sales" THEN "[Amount]*-1"

ELSEIF [G/L Account]="Intercompany Sales" THEN "[Amount]*-1"

ELSE [Amount]
ENDIF

What should I do differently? Thanks in advance!

[PhilipMannering]

Hi @KyraM 

This should sort you out. Your numbers were a bit awkward due to the values being strings.

[Emil_Kos]

Hi @KyraM,

Maybe you prefer using the join tool. I think it will be easier for you to maintain:

The output:

[messi007 · Accepted solution]

@KyraM,

Please see below

Attached the workflow

f this solves your issue please mark the answer as correct.

Regards

[Emil_Kos]

Hi @KyraM,

Regarding your solution. I think you just need to made a small amendment:

IF [G/L Account]="Trade Sales" THEN [Amount]*(-1)

ELSEIF [G/L Account]="Intercompany Sales" THEN [Amount]*(-1)

ELSE [Amount]
ENDIF

You need to remove brackets from the positions that you want to multiply: "[Amount]*-1"

[KyraM]

Thanks! I didn't even notice I put brackets, but without it it seems to work, as well as the reply just above. Thanks everyone!