topic Is it a bug if the Set Analysis includes all rows where a field with only Null values is referenced? in App Development

Is it a bug if the Set Analysis includes all rows where a field with only Null values is referenced?

mplautz — Mon, 05 Feb 2024 16:31:42 GMT

I just came across this interesting Qlik behavior. Let's say I have a measure expression based on the following set analysis:

Sum({< County={'US'}, Quarter={'QTD'}, PremierItemFlag={'1'} >}revenue)

Ordinarily I would expect the formula to only include rows where the Country field = US, the Quarter field = QTD, and the PremierItemFlag = 1.

Unexpectedly, with today's data refresh, there was an upstream issue that caused the PremierItemFlag to be set to NULL for every single row. However, the set analysis did not behave the way I would have expected. Rather than excluding all rows from the Set Analysis, because PremierItemFlag was NULL for all rows, and not 1 for any, the set analysis included all rows. In other words, the PremierItemFlag={'1'} portion of the set analysis had no effect, and it appears to be because that field is NULL for every row in that table.

What I want to know is, has anyone else ever seen this behavior before, and is it a bug? Or is it working as designed? I know if this were an SQL query, the PremierItemFlag = '1' clause would always evaluate to false if that column were NULL for every row. But in this case, it seems to be completely ignoring that constraint.

Re: Is it a bug if the Set Analysis includes all rows where a field with only Null values is referenced?

Anil_Babu_Samineni — Tue, 06 Feb 2024 05:44:27 GMT

@mplautz It is important how the flag designed? PremierItemFlag, Is this straight field from DB or some transformation in Qlik?

Re: Is it a bug if the Set Analysis includes all rows where a field with only Null values is referenced?

marcus_sommer — Tue, 06 Feb 2024 09:59:05 GMT

It's working as designed because it's not a where-clause on a row-level else a flagging on a column-level respectively the set analysis worked like a selection. If a field has no values you couldn't select anything which means that such action is ignored.

I think the only way to prevent unexpected results would be an appropriate data-quality handling in the script-level.