Nested IF with OR inbetween

Solved!
Joeiku
Level 2
Nested IF with OR inbetween

Hi guys, I'm struggling to write the following formula:

If the number in Historical FX is empty,

then check whether Contract Currency has value USD or CNY, if so, take the number from "Depreciation CC", else take "Depreciation CC" and divide it by "End Rate"

else, take "Depreciation CC" and Divide it by "Historical FX"

//So the middle paragraph is a nested if statement

My current code is: 

if(numval("Historical FX") == "",

if(strval("Contract Currency") == "USD" || "CNY",

numval("Depreciation CC"), (numval("Depreciation CC") / numval("End Rate")))),

(numval("Depreciation CC") / numval("Historical FX"))

)

Any help would be greatly appreciated. 

Thank you.

Joe

0 Kudos
1 Solution
arnaudde
Dataiker

Hello Joe,

The logical OR operator ||  will work for two boolean statements but you are using one boolean and one string, therefore only the first condition is evaluated.

if(strval("Contract Currency") == "USD" || "CNY",

should be

if((strval("Contract Currency") == "USD") || (strval("Contract Currency") == "CNY")),

Best,
Arnaud

View solution in original post

5 Replies
arnaudde
Dataiker

Hello Joe,

 

 

Your logic looks fine. However it looks like you have one parenthesis too much at line 3 at end of line: 

 

numval("Depreciation CC"), (numval("Depreciation CC") / numval("End Rate")))),

 

 should probably be 

numval("Depreciation CC"), (numval("Depreciation CC") / numval("End Rate"))),

 

Arnaud

0 Kudos
Joeiku
Level 2
Author

Hi Arnaud, 

Unfortunately the nested if statement (the middle two lines) still aren't returning anything. I'm receiving error "ExpressionError: Cannot parse to number: null". The outer if statement appears to be working.

Thanks again,

Joe 

0 Kudos
arnaudde
Dataiker

Hello Joe,
This means you have nulls in one of your columns  "Depreciation CC", "End Rate", "Historical FX". You can add a previous step "Fill empty with..." according to what you want, to avoid that

Arnaud

0 Kudos
Joeiku
Level 2
Author

Hi Arnaud

Thank you for your help so far!

Joseph

0 Kudos
arnaudde
Dataiker

Hello Joe,

The logical OR operator ||  will work for two boolean statements but you are using one boolean and one string, therefore only the first condition is evaluated.

if(strval("Contract Currency") == "USD" || "CNY",

should be

if((strval("Contract Currency") == "USD") || (strval("Contract Currency") == "CNY")),

Best,
Arnaud