Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
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

AccountAmount
70007100
70007300
20010456
2000010500
700078
4461241514
786238500
532524900
70007600

 

 

 

Expected output

AccountAmount
70007-100
70007-300
20010456
2000010500
70007-8
4461241514
786238500
532524900
70007-600

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@RAJAYKUMAR001 ,

 

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

Hi @RAJAYKUMAR001,

 

For the second part of your question you could use:

 

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

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan

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.

 

 

Please help me with the expression if Contains() Only.

Highlighted
Alteryx Certified Partner

Hi @RAJAYKUMAR001,

 

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

 

If this solves your issue please mark the answer as correct, if not let me know!

 

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

 

If this solves your issue please mark the answer as correct, if not let me know!


Regards,

Jonathan

Labels