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

Alteryx formula assistance for a newbie

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

Developer Tools

Accepted answers

Biswarun

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

All comments

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%20Expressions

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

Ozzym

Thank you guys

Biswarun

@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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703