topic Re: Formulae not working in IF statement in QlikView

Formulae not working in IF statement

braham — Mon, 17 Feb 2025 08:53:14 GMT

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

Re: Formulae not working in IF statement

diegozecchini — Mon, 17 Feb 2025 09:34:14 GMT

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()

Re: Formulae not working in IF statement

braham — Mon, 17 Feb 2025 11:14:07 GMT

Thanks, let me try that and I will revert.

Regards

Re: Formulae not working in IF statement

braham — Tue, 18 Feb 2025 19:32:13 GMT

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.

Re: Formulae not working in IF statement

diegozecchini — Wed, 19 Feb 2025 10:53:06 GMT

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.

Re: Formulae not working in IF statement

marcus_sommer — Wed, 19 Feb 2025 11:42:46 GMT

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.

Re: Formulae not working in IF statement

braham — Tue, 25 Feb 2025 17:33:02 GMT

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.

Re: Formulae not working in IF statement

gq — Tue, 25 Feb 2025 18:16:07 GMT

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

Re: Formulae not working in IF statement

marcus_sommer — Wed, 26 Feb 2025 06:18:15 GMT

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.

Re: Formulae not working in IF statement

braham — Fri, 28 Feb 2025 10:20:45 GMT

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.

Re: Formulae not working in IF statement

marcus_sommer — Fri, 28 Feb 2025 12:02:14 GMT

IMO it's not related to the if() else to the fact that now not n separate views are there else they will be combined. This means these views needs to have a valid association to each other - and quite probably that's not the case in your scenario.

A quite common example for such challenges are views of sales against budgets. The operative data-sources are usually a fact-table for each one - and in nearly each case they couldn't be directly linked because not each sales were planned and reverse the same that not each budget has appropriate sales.

This means there are missing key-values on both sides which are not solvable - no way, never - at least not directly. The reason is that there is no association respectively the association is NULL. But this couldn't be queried and therefore fetched - only cells could be queried if they have a value or are NULL or EMPTY.

I assume that your issue has similar reasons. If so you will need to adjust the data-model - which is often not difficult. Regarding to the sales/budget example above it could be quickly solved by concatenating the sales and budget facts into a single fact-table by harmonizing the field-names and data-structures as much as possible and adding the source-information within an extra field.