topic Expression Challange in QlikView

Expression Challange

brf10043 — Wed, 25 Nov 2020 16:16:04 GMT

All, I have the following expression. I'm finding that when I use the expression below in a table I'm getting unexpected results. Not all records have a value in the Emergency_Flag column. It's either "Yes" or null. When there are no records in the data with an Emergency_Flag value of "Yes" the table brings back incorrect records. However, when there is at least 1 record with an Emergency_Flag of "Yes", the table is correct.

Expression:

Only({<MonthName = {$(vCurrentMonth)}
, Emergency_Flag = {"Yes"}
,Vulnerability -= {"*EOL/Obsolete*"}
,Q_QID = {"=Len(Trim(Centrally_Managed_Flag)) = 0"}
>} Vulnerability)

I suspect that the issue is with how I'm creating the Emergency_Flag in the load script. See the example below. I am left Joining the Emergency Data on to an existing data set that doesn't all ready have the Emergency_Flag column. So the values are either "Yes" or Null in the final data set.

Left Join (Qualys_Data

LOAD //QID as E_QID,
QID as Q_QID,
//[Vulnerability Name] as E_Vulnerability_Name,

[Responsible Party]as E_Responsible_Party,
Date(Floor(Num([Start Date] )),'MM/DD/YYYY') as E_Start_Date,
Date(Floor(Num([Due Date] )),'MM/DD/YYYY') as E_Due_Date,
//[Emergency Flag] as E_Emergency_Flag,
'Yes' as Emergency_Flag

What do I need to do to make this expression work correctly? I'm stuck!

Re: Expression Challange

sunny_talwar — Fri, 12 Jan 2018 12:35:12 GMT

When there are no records in the data with an Emergency_Flag value of "Yes" the table brings back incorrect records.

This is when you make selection and the selected data have all Emergency_Flag which are Nulls?

Re: Expression Challange

marcus_sommer — Fri, 12 Jan 2018 15:04:18 GMT

It's a known behaviour that a set analysis condition on a field which has no values isn't considered as condition in any way. As far there is only one real value it will work like expected. I remember some discussions about it but not if it should be considered as a bug or not.

Anyway I would solve the issue within the datamodel by replacing the join with a mapping. You could concat several of your fields with a delimiter like this: Field1 & '|' & Field2 & '|' Field3 and splitting them with subfield() again to avoid several mappings by merging multiple fields. The essential point here is that the applymap() has a third parameter for the non-matching keys which could be set here to 'no' (whereby more performant would be to replace yes/no with 1/0).

- Marcus