identifying missing records

[Jerry Yeargain]

Very new to Spotfire. I have a situation where I am trying to identify when a required "code" (record) has not been entered. I have one file that contains a key of columns A, B, C and a specific "code" entered in column D, along with other specific information entered. Arecord for each code entered is created. I have a second file that contains a key of column A (matches to column A of the first file) and a record for each "code" that is "required". I need to identify when the first file does not have all of the "required codes" entered and show which "required codes" are missing. I tried creating a file that used columns A, B, C and a record for each "required code". Then used "Add Columns" with a full outer join with the first file to insert a record for any "required code" that did not match with columns A, B, C and the "required code". This appeared to work, but it only brought over the columns I matched on, and put null in all other columns. The null columns caused an issue with filtering in the dashboards i have created. Is there another approach in Spotfire that would work better, or would this best be handled using SQL coding Hope this made sense1

 

I have created threefiles that have the situation that i am looking at. FILE1 contains the list of required codes based on Key1. The second file contains what has been entered so far. The third file contaiins what FILE2 should eventually look like. As you can see, there are a number of codes missing in FILE2. Trying to provide a dashboard that shows the required codes that are missing. As Istated earlier, i tried building FILE3 and using an join to insert missing records into FILE2, but it would not bring over the other data fields, which are used in other filtering options. Any help would be appreciated. Thanks!

[David Boot-Olazabal]

Hi jyeargain,

 

Could you share the files (or dxp file) that you are using This would make it easier to have a look at it and see if there are possible ways to solve this in Spotfire.

 

Kind regards,

 

David