Alteryx Designer Discussions

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live!

SOLVED

If condition formula

Highlighted
7 - Meteor

Hi Guys,

I have been trying to figure out a conditional formula for my output.

My formula is simple that if the account number is 70007 I want its respective number to be negative.

hence I use the below expression but it seems to throw an error.

IF Contains([Account],"70007") THEN [Amount] = - [Amount]

ELSE [Amount] ENDIF

Input file

 Account Amount 70007 100 70007 300 20010 456 2000010 500 70007 8 4461241 514 78623 8500 532524 900 70007 600

Expected output

 Account Amount 70007 -100 70007 -300 20010 456 2000010 500 70007 -8 4461241 514 78623 8500 532524 900 70007 -600

Highlighted
Alteryx Certified Partner

If the Account is a STRING, you can use contains().  So try this and if needed, change the type of Account to a DOUBLE using a select before the formula.

``````IF [Account] = 70007 THEN - [Amount]
ELSE [Amount]
ENDIF``````

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

Hey There,

What should be the expression if I have to do it for 70007 & 80007

I tried these but it did not work.

``````IF [Account] = 70007 or 80007 THEN - [Amount] ELSE [Amount] ENDIF

IF [Account] = 70007 and 80007 THEN - [Amount] ELSE [Amount] ENDIF``````

Highlighted
Alteryx Certified Partner

For the second part of your question you could use:

IF [Account] IN(70007, 80007) THEN -[Amount] ELSE [Amount] ENDIF

Regards,

Jonathan

Highlighted
7 - Meteor

Nope,

Its not working either.

My need is

IF the ACCOUNT Column has these 2 account numbers (700007,800007) I want their sign to be opposite, If any other number then it should remain same.

Highlighted
7 - Meteor

Its Working ,

Thanks

Highlighted
7 - Meteor

Yes My Account is a string because it has 0 before few account number so I would like to retain the zero's as it is (70007, 80007,03007,04007 Etc)

If I Change them to Double I am Missing the 0 before the account number which I do not want to.

Highlighted
Alteryx Certified Partner

Well can you not leave your [Account] column as a string and use the following formula? I've adjusted it slightly to include "" which will class the values as strings and allow you to have values with leading 0s for example.

IF [Account] IN("70007", "80007") THEN -[Amount] ELSE [Amount] ENDIF

Regards,

Jonathan

Highlighted
Alteryx Certified Partner

If you really want to go down the route of the Contains() function you could use:

IF Contains([Value], "800705") THEN -[Amount]
ELSEif Contains([Value], "700082") THEN -[Amount]
ELSE [Amount]
ENDIF