Question on including NULL in SET Analysis

[#1]

Hi, can't wrap my head around the below NULL vs SET Analysis issue, so hope that someone can explain this to me:

attached a simple 3x table structure which contains an Amount and Flag (either 1 or NULL) on tab1 and a Group on tab3. Using the info found on Community, among others the helpful blogs from Henric Cronström , I am able to include NULL in SET Analysis using SUM({$-<FLAG = {"*"}>} AMT). However for some reason the Group field (Group_tab3) shows NULL when used in combination?

For the record: I know the possible workarounds are adding Group value to tab2 or using SUM({$<AMT ={"=IsNull(FLAG)"}>} AMT) instead, however I really would like to learn the reasoning behind the Group_tab3 showing NULL in attached example?

Thanks in advance for your input!

Regards!

Chris

[rwunderlich]

That's an interesting one. I don't know the answer but I note a different behavior if selections are made. For example, if you select values in the AMT field. I hope Henric or perhaps Oleg Troyansky will weigh in.

-Rob

[Oleg_Troyansky]

Why can't I see the attachment? I'd love to see the described behavior.

I can't say that I fully understand the underlying logic around selecting NULLs with Set Analysis. The most common belief is that you can't select NULLs and therefore you can't use Set Analysis to select NULL values.

That being said, the subtle difference is - we can't select FLAG value =NULL, however we can select Amounts, associated with the FLAG=NULL.

I could maybe come up with a more specific explanation if I could play with the actual example.

cheers,

Oleg Troyansky

[Oleg_Troyansky]

Hmm, my Chrome didn't show the attachments, while IE did... very strange...

I played with the example, and I believe that it looks like a bug. I tried selecting different combinations of %KEY, and depending on the selections, sometimes GROUP_tab3 was populated for both relevant rows, sometimes only for one of the two, and sometimes never.

I could not find a logical explanation for it. I recommend reporting it to Support as a bug and see what they say.

cheers,

Oleg

[#5]

Thanks for the feedback, I will give it a try with Support...

Will let you know when I receive some feedback.

Regards

Chris

[Peter_Cammaert]

I managed to translate what Oleg stated into pictures by explicitly showing links and some unexplainable ("ïllogical") behavior:

It seems as is QlikView tries to stuff two values into a single cell, because the same object shows this when a single AMT vlaue is selected:

So, what is/are the other value(s)? Weird.

Hope you get an explanation from support.

[PradeepReddy]

Hi Chris,

In Script,

NullAsValue Field1,Field2..;

Set NullValue='$';   // Any symbol

Load the script.

Use the symbol($) in expression wherever do you want.

Hope this helps you.

Thanks,Pradeep

[#8]

An update from Qlik Support:

I have evaluated your case and am not able to explain the reason for the unexpected NULL value. For enable further investigation by R&D the case has been reported as bug #68020.

We will update you as soon as we get an conclusions or feedback from R&D.

[#9]

Fyi received feedback from Qlik Support:

Thank you for bringing the case 00247022 (BUG # 68020) to our attention. Our R&D (research and development department) has closed this BUG as working as designed (WAD).  Following is the explanation provided by R&D:

“  This negation is based on records (rows in tables), not selections as such, and “GROUP_tab3” is located in a table separated from the “Flag” field by an intermediate link table. This means that a negated SET -expression will only return a value for the dimension “GROUP_tab2” since all information that is required (in this case) can be found in either “Tab1” or “Tab2”, making “GROUP_tab2” a plausible dimension. This, however, does not apply to the dimension “GROUP_tab3” that is entirely dependent on the one-to-many relation between the tables “Tab2” and “Tab3”, making “GROUP_tab3” an entirely unsuitable dimension in this case…

One way to get around this would simply be to join the two tables “Tab2” and “Tab3”, in which case the relation becomes entirely symmetrical and both “GROUP_tab2” and “GROUP_tab3” would be fully valid dimensions.

Another simple way is of course to modify the expression along the lines that was originally suggested as a workaround:

SUM({$<AMT ={"=IsNull(FLAG)"}>} AMT)

…which is plausible since this expression does not require the SET-expression to be negated, instead this is carried out as a condition within the SET-expression.

Another, simpler, and in fact the recommended way to solve this, would be to use the following expression:

sum(if(FLAG<>1,AMT))

…that does not require any negation of a SET-expression at all, but simply conditional statement within the aggregation…   ”