If condition formula

AJ_AJ

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

Developer

Accepted answers

MarqueeCrew

@AJ_AJ ,

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

Jonathan-Sherman

Hi @AJ_AJ,

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

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

All comments

MarqueeCrew

@AJ_AJ ,

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

AJ_AJ

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

AJ_AJ

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335