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.
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.
LOAD//QID as E_QID, QIDasQ_QID, //[Vulnerability Name] as E_Vulnerability_Name, [Responsible Party]asE_Responsible_Party, Date(Floor(Num([Start Date] )),'MM/DD/YYYY') asE_Start_Date, Date(Floor(Num([Due Date] )),'MM/DD/YYYY') asE_Due_Date, //[Emergency Flag] as E_Emergency_Flag, 'Yes' asEmergency_Flag
What do I need to do to make this expression work correctly? I'm stuck!
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).