Using If with {}

Question

Hey everyone I'm trying to use an IF statement to return TRUE if an account is one of 2 types and in shortfall using

=if(ri!portfolio<>{"type1","type2"},false,if(trim(ri!arrearsStatus)="Shortfall",true(),false()))

but if I pass type1 and Shortfall this produces as False, strangely if I use

=if(ri!portfolio<>{"type1"},false,if(trim(ri!arrearsStatus)="Shortfall",true(),false()))

passing type1 and shortfall returns True.

Any ideas how I would fix the first statement?

Thanks

Krishna Chaitanya · Accepted Answer

if( or( ri!portfolio = "type1", ri!portfolio = "type2" ), if( trim( ri!arrearsStatus ) = "Shortfall", true(), false() ), false() )

PhilB · Answer

I would like to clarify what is happening here, as there are a couple of things going on. 
 First, operators can handle lists. For example, {1, 2, 3, 4, 5) > 3 will return {false, false, false, true, true} as the > operator will compare 3 to every item within the list. in your example, ri!portfolio is compared to every item in {"type1","type2"} and so you end up with a list containing two booleans - for example {false, true} where ri!portfolio is "type1". 
 Second, and following on from this, if() can also handle lists! This is documented , but if() will iterate over each item in the array it is passed and if the item is true, return the result of its 2nd parameter, or the result of the 3rd parameter if the item is false. Note that the second and third parameters in your example are not lists, so one or the other is returned depending on the state of the item. If your 2nd and 3rd parameters were lists, if() would return the appropriate item in each list depending on the state of the item - for example, if({true, false, true}, {"a","b", "c"}, {1, 2, 3}) will return {"a", 2, "c"}. 
 Finally, your nested if() - specifically if(trim(ri!arrearsStatus)="Shortfall",true(),false()) - is entirely unnecessary. I see this a lot, but if you ever write if(condition, true, false) then DON'T! Ultimately a condition for an if() should always return a boolean, so returning booleans as both the 2nd and 3rd parameters shouldn't ever be required. Additionally, if you need to reverse the result of the condition, just use not(). 
 Ultimately your expression should be: 
 if(
 contains(
 {"type1", "type2"},
 ri!portfolio
 ),
 trim(
 ri!arrearsStatus
 ) = "Shortfall",
 false
)

Using If with {}

Top Replies