Add Calculated Column based on value in second table
I've not used Spotfire in some time and having trouble figuring out what I think should be an easy problem.
I want to create a new column based on the lookup (over) the value from a second table. I have an ID field in both tables. What I want to do is lookup the ID in second table and go OVER all values and then use some CASE statement set the output value. I believe to make this work I need to create some new table with the results of the joined tables. Ultimately I want to be able to MARK or Filter on the value from this result table and it will show the associated rows from data table.
I figured it would be best to create a simple Spotfire file to show example of what I'm wanting. In the example I have Table 1 and Data Table data available. I'm wanting to create the Result Table. In example I did the Result Table by hand for show.
Normally I would just create a view in SQL DB and take care of this problem before getting to Spotfire but I'm importing data from fixed Excel tables and don't have this option at moment. So need to see if I can solve with Spotfire.