topic Re: Set modifier problem with empty column in QlikView

Set modifier problem with empty column

jcarpenter9 — Wed, 10 Dec 2014 22:26:28 GMT

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

Re: Set modifier problem with empty column

simenkg — Thu, 11 Dec 2014 01:47:22 GMT

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

Re: Set modifier problem with empty column

hic — Thu, 11 Dec 2014 08:42:29 GMT

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

HIC

Re: Set modifier problem with empty column

rbecher — Thu, 11 Dec 2014 09:37:26 GMT

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

Re: Set modifier problem with empty column

jcarpenter9 — Thu, 11 Dec 2014 13:57:22 GMT

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

Re: Set modifier problem with empty column

jcarpenter9 — Thu, 11 Dec 2014 14:02:50 GMT

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

Re: Set modifier problem with empty column

rbecher — Thu, 11 Dec 2014 14:06:31 GMT

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

Re: Set modifier problem with empty column

jcarpenter9 — Thu, 11 Dec 2014 14:16:53 GMT

Logged as support case 00400014.

Re: Set modifier problem with empty column

jcarpenter9 — Thu, 11 Dec 2014 14:20:39 GMT

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.

Re: Set modifier problem with empty column

hic — Fri, 12 Dec 2014 09:58:16 GMT

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

Re: Set modifier problem with empty column

jcarpenter9 — Mon, 22 Dec 2014 16:09:57 GMT

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.

I asked them to reconsider their decision.

- James

Re: Set modifier problem with empty column

rbecher — Mon, 22 Dec 2014 17:58:54 GMT

I think unexpected behavior is a show stopper for Set Analysis..

- Ralf