Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
mplautz
Contributor III
Contributor III

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

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.

Labels (1)
2 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
marcus_sommer

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.