Spotfire lookup a value from a calculated column

Question

I have a data set in spotfire where I'm creating a calculated column that returns an entity name (the entity name being returned is also in column A). Once the entity name is returned from the calculated column, I need a data attribute that is in a different column but is on the row of the entity name.

I've attached a snapshot of data. The calculated field returns 'Company C'. I need to create a column that looks up company C in column A and returns the location of company C.

In excel I would use a VLOOKUP to lookup company C on columns A:C and return column 3. But I can't figure out how to do this in Spotfire. Any help?

Answer 1

@Carson- My answer is slightly similar to that of @blakeoft.

Here is my observation. It is difficult to lookup values from columns in the same table in Spotfire. Data tables in Spotfire act more like database tables. However, we can achieve the desired result by adding a little step.

Here are the steps:

Step 1: Create a table by pivoting your existing table with two fields Company and Location. Let's name this as Lookup table.

Lookup table screenshot:

Step 2: Now, add Location column to your existing table from Lookup table by joining the two tables with Calculated field in your existing table and Company in your Lookup table as shown below. Select Location field which you would like to add to your existing table.
Note: Ensure the join method selected is 'Left outer' which is a default selection.

However, you should be careful while using a calculated field as a primary key to connect two tables. Please check this SO post.

spotfire: search a column of another table

Final Table with Location added:

Answer 2

I don't believe you can add columns with one of the join criteria being a calculated column, unless you calculate the column when you're reading in the dataset. So let's assume that you did in fact add this calculated column when you read in the data.

You would click Insert > Columns... Then choose your table in the Add columns to data table drop down box, as well as choosing the same table in the Add columns from menu. Click Next. On the left side, click CalculatedField to select it, then click Company on the right to select it. Press Match Selected and hit Next. Select Location and click Finish. This will embed your table into your analysis (I don't like doing this). You could also add a copy of your table to your analysis and choose it in the Add columns from menu to avoid the embedding. You'll also probably want to rename the new column as it will be called Location (2).

I'm on Spotfire 6.5, so the button names might vary.