IF formula issue

I am trying to input the formula from excel:

=IF(OR([@[Ending Balance Trx CCY]]=0,[Trx CCY]="USD"),1,[@[Ending Balance Func CCY]]/[@[Ending Balance Trx CCY]])

Into my alteryx work flow the only was i can think of doing it is as follows:

IF [Ending Balance Trx CCY]='0' THEN '1' ELSEIF [Ending Balance Trx CCY]>'0' Then ([Ending Balance Func CCY]/[Ending Balance Trx CCY]) ELSEIF [Ending Balance Trx CCY]<'0' THEN ([Ending Balance Func CCY]/[Ending Balance Trx CCY]) ENDIF

however this brings back a Malformed if Statement error. I am unsure where i went wrong? the data is double on all fields so its not a string type issue?

Thanks in advance

Data Investigation

Accepted answers

LukeG

Hi @MMG

Try this statement:

IF [Ending Balance Trx CCY]= 0 OR [Trx CCY]="USD"
THEN 1
ELSE [Ending Balance Func CCY]/[Ending Balance Trx CCY]
ENDIF

Be sure to pay attention to datatypes. The datatype for the new field should be numeric. The datatypes for [Ending Balance Trx CCY] and [Ending Balance Func CCY] should also be numeric. [Trx CCY] should be a string.

I hope I interpreted this correctly. If there are any questions, let me know!

Luke

All comments

CarlDi

Hi @MMG - try removing the single quotes around the numbers of your alteryx statement. As you said, your types are double and placing quotes around them convert them to strings. hope that helps!

Martyn

@MMG

I think the main issue is there is no ELSE in your logic.

IF .....

THEN.....

ELSEIF.....

THEN.....

ELSEIF.....

THEN....

ELSE.....

ENDIF

LukeG

Hi @MMG

Try this statement:

IF [Ending Balance Trx CCY]= 0 OR [Trx CCY]="USD"
THEN 1
ELSE [Ending Balance Func CCY]/[Ending Balance Trx CCY]
ENDIF

I hope I interpreted this correctly. If there are any questions, let me know!

Luke

MMG

That was the solution thank you very much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703