Search code examples
sqlcalculated-columnsspotfire

Spotfire - Getting data from one table that falls between two dates in another table and adding to a calculated column

What would be the expression to create a calculated column in Table Example 2 called "SZODMAXCALC", that would contain the SZODMAXCALC from Table Example 1 given that the data from Table Example 1 falls between the dates (DTTMSTART and DTTMEND) within Table Example 2?

Maybe this is easier done on the SQL side that loads the data?

Table Example

Solution

  • there is no way to create a calculated column that references a column in another table.

    you will need to do a join either in Spotfire (via Insert...Columns)* or on the SQL-side of things (either via a view on your database or by creating a new information link in Spotfire).

    the best method depends on your data structure, implementation, and desired results, so I'm not able to recommed there. take a look at both options and evaluate which one works best.

    * NOTE that Spotfire cannot join based on a Calculated Column as a common key. that is, using your example, if [WELLNAME] is a calculated column, you cannot tell Spotfire the equivalent of SELECT wellname, ... FROM table_a LEFT JOIN table_b ON table_a.wellname = table_b.wellname.

    the alternative is to Insert...Transformation and choose Insert New Calculated Column, and to join on that instead.

    the reason for this is that calculated columns are very mutable; they could change frequently based on a user action. it would be inefficient to re-execute the join each time the column's contents changed. conversely, a "Transformation Calculated Column" is only updated when the data table is loaded.