Formulae not working in IF statement

[braham]

Morning

I hope that I will be able to get some insight into my issue with the assistance of those more knowledgeable than me. I am running QlikView May 2023.

I have a scenario where I need to test to see if a value of a name field exits in table A and if not use the value in table B for a dimension. If it exits in table A I will use it. To do this I use the IF statement combined with the IFNull to test if field A exists. Here is the code:

=If(IsNull(Aggr(FirstSortedValue({$<BGDRELATION = {'M'}>} Distinct SchemeName_MA, -BGDDATE), BGDMEMBERNO)),
 Aggr(FirstSortedValue({$<MMD_ActiveMemberRaised = {'1'}, MMD_Relationship = {'M'}>} Distinct SchemeName, -LNK_CCYYMM), LNK_Member),
Aggr(FirstSortedValue({$<BGDRELATION = {'M'}>} Distinct SchemeName_MA, -BGDDATE), BGDMEMBERNO))

An added complication is that the Field A is stored by month and can therefore change over time. When reporting over more than 1 month I want to pick up the last value (hence the FirstSortedValue).

My frustration with this statement is that for a very small percentage of the records I get a Null value being reported. When identifying one record that returns a null, I look at the values of the name field in both records and there are valid values. When I only run the code to extract from a file A I get the correct answer and when I only run the code to only extract from file B I also get the correct answer. Each of the statements works when not part of the IF statement, but when put in the IF statement it returns a Null instead of the correct value.

I changed the If statement to return a string 'Is Null' or 'Is not Null' and when the value is there (in my selected example) I get 'Is not null', which is what I expect, so the If test seems to be applied.

Has anyone got some insight into what is happening when QlikView processes an if statement that may provide some insight as to why I am not getting the correct answer? 

Thank you in advance for your assistance.

Regards

Braham Edwards

[diegozecchini]

Hi!
reason is still unknown to me, but I think it is a common issue in QlikView where Aggr() and FirstSortedValue() behave differently within an If() statement compared to when they are used individually.

An idea is if FirstSortedValue() is returning null due to duplicate values, to use Only() inside Aggr():
Aggr(Only({$<BGDRELATION = {'M'}>} SchemeName_MA), BGDMEMBERNO)

or use len() instead of IsNull():
If(Len(Aggr(FirstSortedValue({$<BGDRELATION = {'M'}>} Distinct SchemeName_MA, -BGDDATE), BGDMEMBERNO)))=0,

This often works better inside If() than IsNull()

[braham]

Thanks, let me try that and I will revert.

Regards

[braham]

Hi,

Had an opportunity to test these 2 solutions. Unfortunately, neither returned the desired results. Both returned the correct answers when executed outside the If statement.

Thanks for the suggestion.

[diegozecchini]

Hi!
If available, Coalesce() can be more efficient than If() for handling nulls.

Coalesce(vSchemeNameA, vSchemeNameB)

Other idea could be to define each part separately in variables and use them in the If() statement to debug what is returning null.

[marcus_sommer]

What happens with the following (simplified) approach as calculated dimension:

aggr(coalesce(
   Firstsortedvalue(...), Firstsortedvalue(...), concat(...), concat(...), '#NV'),
Dim)

It will return the first not NULL result whereby the concat() is aimed to display the n possible values which leads to a NULL result by a firstsortedvalue() and the last is a general default. You may also add an extra order-information to the output, like:

... '1. ' & firstsortedvalue(), '2. ' & firstsortedvalue(), ...

to identify exactly from where which result is coming. And the next step might be to add n further dimensions to the table to see more details.

[braham]

Thank you everyone for the suggestions. I have tried the coalesce option, but still do not get the correct answer. I will raise it as a bug with Qlik via our VAR.

[gq]

Ditto, for me most of the times Len function gave me accurate results than IS NULL in calculations.

[marcus_sommer]

Why should there be a bug? Just because there are some results NULL? If there is any NULL it means that the table-associations respectively the data-model isn't suitable and/or the data-set hasn't the data like expected.

[braham]

Hi Marcus, Thanks for the response. The data is as expected, that is why I need to check for null values. I am trying to understand why the statements deliver the correct results when executed outside the if statement but fail when executed in the if statement. Perhaps I am not understanding how QlikView evaluates an if statement.