Jump to content

identifying missing records

Jerry Yeargain

Recommended Posts

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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...