Set modifier problem with empty column

[jcarpenter9]

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.

-James

[simenkg]

Just set the flag as if(Expression1>=1001,1,0) as Over1000Flag.

[hic]

I suggest you report this to support. My gut feeling is that it is a bug.

HIC

[rbecher]

I think this is a very dangerous issue and unexpected behavior.

However I tend to not use flags at all since they consume memory espacially in fact tables. A set analysis search expression seems to work in that case:

=Sum({ $ <Expression1={">=1001"}>} Expression1)

- Ralf

[jcarpenter9]

Thank you, Henric. I will follow up with support.

[jcarpenter9]

Thank you for the feedback. I agree that flags do consume memory. However, the best practice as taught in QlikView's training course is to use summable numeric flags for better online set analysis performance, as opposed to text searches.

In the actual client application we need to use flags for another reason, because the search values will change occasionally. I want to avoid modifying expressions and variables across the end user applications if I can modify a single QVD creator step to handle new search values.

-James

[rbecher]

Hi James,

I do not agree with a lot of so called "best practices" people are teaching around. Most of them are already outdated or false. However, you could also solve your problem with storing set analysis conditions into variables..

- Ralf

[jcarpenter9]

Logged as support case 00400014.

[jcarpenter9]

Not all best practices are actually 'best'. But as long as the official QlikView training materials teach a certain technique, the product ought to actually support using that technique accurately, I think. Whatever workaround turns out to work best, a set modifier condition for which zero rows are true ought not to return all rows.

[hic]

I would still recommend using flags. The additional memory a flag uses is minimal, also in a fact table. And the advantage is often clarity and manageability of your formulas.

Ralf is right, though, that a Set Analysis expression containing a search, e.g. {$<Expression1={">=1001"}>}, also is very efficient. I would expect the performance difference between the two to be almost negligible.

HIC