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

[marcus_sommer]

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.