Trying to use IF, ISNULL and ELSEIF multiple times in one Formula

[AdeHowe]

Hi everybody,

My formula below gives me a Parse Error and I'm unsure why?

Basically, i have columns with dates in, and would like to use a specific column that does not have a Null in it.

Below I'm trying to say, if two columns show nulls, then use a specific column in the FX formula.  However if only one column shows null, then use a different column in the FX formula, and to make it a bit more complex if none show as null, then use another column in the FX formula.

Am i missing something like parentheses around some of the code, or is it just plain wrong!  Any help would be gratefully received.

if IsNull([FX Rate Date Standard]) and IsNull([FX Rate Date Bank Holiday]) then

(if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Long Bank Holiday AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Long Bank Holiday SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Long Bank Holiday USD]

else

if IsNull([FX Rate Date Standard]) then
if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Bank Holiday AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Bank Holiday SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Bank Holiday USD]

else

if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Standard AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Standard SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Standard USD]

Endif

[CarliE]

Hi @AdeHowe 

if IsNull([FX Rate Date Standard]) and IsNull([FX Rate Date Bank Holiday]) then

if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Long Bank Holiday AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Long Bank Holiday SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Long Bank Holiday USD]

else

if IsNull([FX Rate Date Standard]) then
if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Bank Holiday AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Bank Holiday SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Bank Holiday USD]

else

if [Currency]="GBP" then [Value in Local Currency] elseif
[Currency]="AUD" then [Value in Local Currency]/[Standard AUD] elseif
[Currency]="SEK" then [Value in Local Currency]/[Standard SEK] elseif
[Currency]="USD" then [Value in Local Currency]/[Standard USD]

Endif

There was an extra parentheses in there -- try this! Also, you may need to endif multiple times due to the multiple if statements in the formula

If this helped to solve your problem, please make sure to mark it as a solution

[AdeHowe]

Hi @CarliE 

Thanks for the help.  I removed the extra parentheses, now Alteryx likes the formula all the way down to the Endif where the text becomes black and i get the Malformed IF statement message.

Will try adding the multiple Endif's

[CarliE]

@AdeHowe 

if you send some dummy data and the expected output, i can help to troubleshoot!

[Emmanuel_G]

@AdeHowe 

For the malformed if function persists, try to ensure that you have as many as ifs as endifs.

If you want to make many elseif. Do not separate else and if. 

When you separate both, you create new if function which must end with endif. But if this is really what you want, make sure you have as many as ifs as endifs.