Alteryx formula assistance for a newbie

[Ozzym]

Hi 

I was looking for a little assistance

trying to use the “formula” function that is likely an IF function that looks up a value in one column and then changes the amount on another column to a minus figure

example is below so on the “DCIP” column IF there is either a LC or a SC it would change the amount in the “amount” column to a negative figure. I see functions like the below not sure how it works even after looking at a few articles maybe because my excel skills are not brilliant to start with

if anyone can help would really appreciate it

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF

thanks

Company	Account Pool Name	DCIP	Currency	Amount	Value	Age	Entry
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	8,717.83	01 Mar 2022	118 days	01 Mar 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	157,386.00	01 Mar 2022	118 days	01 Mar 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SC	CHF	418,284.85	13 Jun 2022	14 days	13 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	15,459.00	21 Jan 2022	157 days	21 Jan 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	90,271.22	20 Jan 2022	158 days	20 Jan 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SD	CHF	79,058.22	20 Jan 2022	158 days	20 Jan 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SC	CHF	836.20	17 Jan 2022	161 days	17 Jan 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	8,100,964.24	07 Jun 2022	20 days	07 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SC	CHF	450,590.25	03 Jun 2022	24 days	03 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	121,562.00	29 Oct 2021	241 days	29 Oct 2021
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	12,729.75	30 Sep 2021	270 days	30 Sep 2021
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	26,871.85	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	120,770.00	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	2,179,390.61	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	213.68	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LD	CHF	2,179,390.62	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	LC	CHF	213.67	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SD	CHF	7.00	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SD	CHF	7.00	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SD	CHF	7.00	24 Jun 2022	3 days	24 Jun 2022
GLOBAL NOSTROS	UBS02300000070861050000YCHF(BBI)	SD	CHF	7.00	24 Jun 2022	3 days	24 Jun 2022

[Sebastiaandb]

Hi @Ozzym ,

Here's the formula you're searching for :-):

IF [DCIP] = "LC"  then -1*[AMOUNT] elseif 

[DCIP] = "SC"  then -1*[AMOUNT] else [AMOUNT] endif 

Greetings,

Seb

[IraWatt]

Hey @Ozzym,

Your looking for a formula like this:

IF [DCIP] = "LC" or [DCIP] = "SC" THEN -[Amount] ELSE [Amount] ENDIF

If the DCIP column equals the text LC or SC then return the ammount as negative else just return the normal ammount. Note I am updating the ammount column in the formula tool highlighted below:

Other things to note is I had to change your ammount colum to a numeric data type with the select tool and remove the ','s as that makes it text not numeric data.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Ozzym]

Thank you guys

[Biswarun · Accepted solution]

@Ozzym Please accept Ira Watt's solution if that helped you.

[IraWatt]

No worries @Ozzym! Thanks @Biswarun 😄 Ozzym you can click the green tick on any replys that helped you find the solution.