0

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.

txemsukr
  • 1,017
  • 1
  • 10
  • 32

1 Answers1

2

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.

blakeoft
  • 2,370
  • 1
  • 14
  • 15
  • Sadly I can't have only one table for technical reasons because one table is generated with user input. – txemsukr Mar 22 '18 at 14:54
  • 2
    @txemsukr is there something preventing you from joining the tables in Spotfire? – niko Mar 22 '18 at 15:25
  • @niko That would add some more complexity. I would have preferred to keep things as simple and maintainable as possible – txemsukr Mar 22 '18 at 15:59
  • 2
    in line with my virtual data table concept (https://stackoverflow.com/a/32089679/4419423), I recommend adding your two tables, then adding the primary table as a reference to itself, and finally inserting the column from the second table to that reference ("virtual") table. Spotfire can of course handle cross-table expressions but I've found that they add weird quirks to the analysis. doing it this way is self-documenting and sustainable. you have to remember that Spotfire's expression language is not SQL and is not designed to behave as such. crossing tables is the complexity to avoid :) – niko Mar 22 '18 at 16:10
  • 1
    @niko Thanks, I thought it was better not to have redundancy of information between tables. I used SQL a lot and I thought the best way to do it was with a SQL approach. I was definitely wrong. I fixed my problem the way described in your answers. – txemsukr Mar 26 '18 at 07:11
  • 1
    glad you got it sorted! yeah it's definitely a shift to go from "SQL/database thinking" to "Spotfire thinking." step by step, though :) – niko Mar 26 '18 at 12:52