If condition formula

[RAJAYKUMAR001]

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

[MarqueeCrew · Accepted solution]

@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 

[RAJAYKUMAR001]

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

[Jonathan-Sherman]

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

[RAJAYKUMAR001]

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.

[RAJAYKUMAR001]

Its Working ,



Thanks 

[RAJAYKUMAR001]

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.

[Jonathan-Sherman · Accepted solution]

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

[Jonathan-Sherman · Accepted solution]

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