Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

brf10043
Contributor

Expression Challange

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!

Tags (1)
2 Replies
MVP
MVP

Re: Expression Challange

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

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