CODEWITHSUNDEEP
expressionspotfiretibco
txemsukr
txemsukr

Reputation: 1037

Spotfire - Custom expression on two tables

I have two tables, one that contain some vehicles with their height, and the other one that contain limits for each category of vehicle.

I want to display, in a bar chart, the number of vehicle that are out of height limit, for each category. For that I need a custom expression.

I tried something but it doesn't work because there are two tables, it works well when there is only one table. For technical reasons, I can't have only one table. Here is my attempt:

sum(If([VEHICLE].[height]>[HEIGHT_LIMIT].[hlimit],1,0) OVER (Intersect([VEHICLE].[category]))

With this I get the following error:

All arguments of the function '>' in the expression must be aggregating when the visualization combines data from different tables.

Here is a little data sample:

VEHICLE TABLE
category | id | height
---------+----+------
   A     |  1 |  1
   A     |  2 |  3
   A     |  3 |  3
   A     |  4 |  4
   B     |  1 |  2
   B     |  2 |  4
   C     |  1 |  1
   C     |  2 |  1

HEIGHT_LIMIT TABLE
category |     hlimit
---------+----------------
   A     |       2
   B     |       3

With this data sample the bar chart should show 3 for category A, 1 for B and 0 for C.

Upvotes: 0

Views: 2707

Answers (1)

blakeoft
blakeoft

Reputation: 2400

First, I want to admit that I've never made a custom expression that addresses more than one table.

My solution would be to insert the hlimit column to the vehicle table with a left outer join, and then create a calculated column that is 1 if hlimit is exceeded, and zero otherwise. Both tables would still remain in the application.

Upvotes: 2

Related Questions