If, then statement

[Lauriek]

Hello,

My formula seems to be correct until I type ENDIF, then it goes black.  I want the last else statement to be Net Gallons as Null.  I can't figure out this issue.

if contains([ProductCode],"130_copy,065_copy,167_copy,
145_copy") then [Net Gallons]*".0002" elseif "227_copy,073_copy" then [Net Gallons]*".0001"
else

Thanks, 

Laurie

[fmvizcaino]

Hi @Lauriek ,

The following function contains([ProductCode],"130_copy,065_copy,167_copy,
145_copy") is only true when you have the full string in your product code.

Also, I've removed the double quotes from [Net Gallons]*0.0002 - numbers shouldn't be between quotes

IF you want to check each of the codes, you need to build your contains functions as follows:

contains([ProductCode],"130_copy) or contains([ProductCode],"065_copy") or contains([ProductCode],"167_copy")
145_copy") 

From what you wrote:

if contains([ProductCode],"130_copy) or contains([ProductCode],"065_copy") or contains([ProductCode],"167_copy")
145_copy")  

then [Net Gallons]*0.0002

elseif contains([ProductCode],"227_copy) or contains([ProductCode],"073_copy")

then [Net Gallons]*0.0001
else null()

endif

Be aware that [Net Gallons] needs to be a number and the formula should be formatted as a number as well.

If this still gives you an error, please share a print screen of your formula and your dataset entering the formula.

Best,

Fernando Vizcaino

[kirans666]

I think you can use the below code snippet to solve your problem. 

IIF (contains([productcode],"130_copy,065_copy,167_copy,
145_copy"),tonumber([Net Gallons])*.0002,IIF (contains([productcode],"227_copy,073_copy"),tonumber([Net Gallons])*.001,NULL()))

Please let me know if you face further problems with it.  

[Lauriek]

Thank you for your help.  I did not get any errors in the formula, but all results are null.

[kirans666 · Accepted solution]

Can you try this? 

I did it considering all of them as one common text 🙂 

IIF (contains([productcode],"130_copy") or  contains([productcode],"065_copy") or 
contains([productcode],"167_copy") or contains([productcode],"145_copy"),tonumber([Net Gallons])*.0002,IIF (contains([productcode],"227_copy") or contains([productcode],"073_copy"),tonumber([Net Gallons])*.001,NULL()))

[fmvizcaino]

Hi @Lauriek ,

I'm attaching a workflow  as example to show you how.

Best,

Fernando Vizcaino

[Lauriek]

That worked!  Thank you so much for your help.