Set Analysis Problem with QS June 2018

[steve_br]

Hi,

I have a problem that occurred after I upgraded to QS June 2018.

My Data is like:

Document,Number

100,1

200,2

300,3

400,

500,5

600,6

700,

800,8

900,9

With QS February 2018 the expression Count({<Number-={'9'}>}Document) would return 8. Yesterday I upgraded to June 2018 and reloaded the data (I did not make any change in the data file or the app). Now the same expression returns 6, i.e. totally ignoring the lines with no Number value as if they do not exist. If I change the script to IF(LEN(TRIM(Number))=0, 'Missing', Number) AS Number

then the expression returns 8 again.

Also, with the previous release, the expression Count({<Number={''}>}Document) would return 2. With QS June 2018 it returns 0 and works again only after the change in the script mentioned above.

Is this a bug in this version or what is going on?

[kfoudhaily]

test null as value; command

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/null-value-handling.htm

[steve_br]

Example of what I am talking about:

Data:

Script:

Result:

As you can see, the first formulas that worked perfectly before, now completely ignore the fields with no value as if they do not exist.

What's wrong, is this a bug? Maybe you guys will know? mto‌ stalwar1‌

Thanks for any help!

[JustinDallas]

Give this post a gander.

https://community.qlikview.com/thread/283180

Long story short, Nulls are handled weirdly by Qlik during Set Analysis.

[steve_br]

Hi! Not sure what to test here, could you please specify? 😕

[steve_br]

Thanks, nice read, but in my case I don't understand how something that worked with a previous version suddenly does not work at all.

[Michael_Tarallo]

Hi Steve - I added hic‌ to this as he may be able to help.

[hic]

I cannot tell whether this is the introduction of a new bug, or whether it is an old one being fixed: I don't know what data you have in your Excel file. The crucial point is whether the blanks are NULLs or zero-length strings.

NULLs cannot be selected, but zero-length strings can. So when you use the set operator -={'9'}, you will select all selectable values except 9. Hence, you will not select the NULLs.

Example:

If data is

[Document,Number

100,1

200,<NULL>

300,<zero-length string>]

then Count( {<Number-={'1'}>} Document ) should return 1. It should match the zero-length string but not the NULL.

HIC

[steve_br]

Hello Henric! Thank you for your answer! I understand that NULLs cannot be selected. However, it is a bit weird that the same app behaves differently in the latest Qlik version.

I did some testing using the same data file and the same qvf file with two QS releases.

QS November 2017 - all empty fields are loaded as zero-length strings and are selectable in a filter pane. The set analysis also works:

QS June 2017 - all empty fields are loaded as NULLs by default, they are not selectable and consequently the set analysis expression does not work as it did before:

Therefore, it looks like the latest Qlik version interprets and loads the same data in a different way. As you said, this might be a bug or fix, but my conclusion is that something has definitely been changed in this Qlik release. However, I could not find any mention in the documentation.

In case this is a bug, I hope that it will be fixed. If it is a fix, I believe that such a change should be clearly communicated as it is kind of a big deal that would impact a lot of people.

Thanks again!

Cheers,

Steve

[jonathandienst]

So its possible the set analysis is not ther problem, but that the LOAD is operating slightly differently on your data - before the upgrade they were treated a empty strings, now they are being treated as nulls.

Just my 2c worth...

Beaten to the punch - so my guess was correct