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!
Solved! Go to Solution.
Hi @KyraM
This should sort you out. Your numbers were a bit awkward due to the values being strings.
Hi @KyraM,
Maybe you prefer using the join tool. I think it will be easier for you to maintain:
The output:
Please see below
Attached the workflow
f this solves your issue please mark the answer as correct.
Regards
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"
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!