Search code examples
searchcalculated-columnsspotfire

spotfire: search a column of another table

I'm trying to make a calculated column in Spotfire, where a specific value has to be present in a column of another table before a new value is assigned.

My expression looks like this:

If(Find('Specific value first table',**'present in second table'**) is Null, 0, 'New Value')

I have no idea how to involve this second table in my search, does anyone has any idea?

Thanks in advance!

Solution

  • You won't be able to reference another table when creating a calculated column. However, I think we can solve your problem all the same.

    What you'll want to do is attempt to insert columns from your other table by joining on the column that holds the values you're searching for and then check if it exists with a simple calculated column. In your case you'll want to set the null values as your new value and keep the existing values. Something like case when [new_column] is null then 'new_value' else [new_column] end.

    Below is a previous similar but slightly different question I answered with my answer copied below for convenience.

    How to Search Table1 Column Value is present in Table2 Column or not in spotfire?

    1. Create a Calculated Column in Table2 named EXISTS with the expression of TRUE.
    2. Go to Insert>Columns and choose to add columns to Table1 from Table2.
    3. Match on your primary key (in my case the column "LETTER")
    4. Select EXISTS from the checkboxes to add and use the Join method of Left single match in order to preserve your number of records and hit Finish.
    5. Now you have a column named EXISTS in your Table1 that is either null or TRUE. If you would like to have it be TRUE or FALSE you can create a calculated column based upon it that evaluates if it's NULL or not like so: [EXISTS] Is Not Null. I named mine "EXISTS_TF" in the example below.

    My data and resulting columns after the above:

    spotfire_example_1

    You should now be able to filter and do calculations based on your new column(s).