2 Replies Latest reply: Jan 12, 2018 10:04 AM by Marcus Sommer RSS

    Expression Challange

    Bobby Flake

      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.



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


      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

          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

            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