I have found a case where a set modifier that searches a column containing all nulls returns ALL rows from the table. I would expect it to return no rows, since there is no row where the search condition is true. This problem appeared when I created a set analysis expression that averaged rows for a field value that has not yet been populated in the source data. We expected to initially see zeros in the chart until the users start to enter the new value, but instead we saw values in the chart for rows that contain no data.
I'm attaching a .qvw that illustrates the problem. It uses the test script that's built into the script editor. In the load script, the field Over1000Flag contains all nulls because no rows have values in Expression1 more than 1000. The field Over1000Flag_v2 also contains nulls; then I concatenated a single row to the table with a character in this field and no other fields. This is the only workaround I've found so far.
In the straight table chart, this expression returns the incorrect sum for all rows in the table, even though no rows contain 1 in the flag field:
=
Sum({ $ <Over1000Flag={1}>} Expression1)
This expression, however, returns zero, which is correct:
=
Sum({ $ <Over1000Flag_v2={1}>} Expression1)
I wanted to get feedback from the community before opening a support ticket / bug report, in case I'm just missing something obvious here.
Here is an update. Qlik Support replied to my bug report that null handling is tricky and that they do not consider this a problem. They suggested a workaround of always ensuring that the column referenced in a set modifier contain non-null values.
I don't agree with this assessment. If a) a column is all nulls, and b) a set modifier searches for rows where that column contains 'y', then c) QlikView should return no rows, not all rows, because the condition is not true or not known for any row.
If on the other hand the set modifier logic only needs the condition to be "not false", this needs to be loudly documented. Likewise, if a column with some nulls is handled differently from a column with all nulls. I don't think that most developers would expect it to work in this manner.
