Spotfire跨双表自定义表达式开发：统计超限车辆数量

Got it, let's work through this cross-table calculation problem since you can't merge the two tables directly. The key here is to link the vehicle table to the height limit table via the model field to pull in the correct limit for each vehicle, then count how many exceed that limit per model.

Adjusted Custom Expression #

Here's the revised expression that should work across your two tables:

SUM(
  IF(
    [VEHICLE].[height] > LOOKUP([HEIGHT_LIMIT].[max_height], [HEIGHT_LIMIT].[model] = [VEHICLE].[model]),
    1,
    0
  )
)

Breakdown of the Expression #

• LOOKUP(...): This function bridges the two tables by matching the vehicle's model to the corresponding entry in the height limit table. It fetches the max_height value that applies to the current vehicle's model—this is what was missing from your original single-table expression.
• IF(...): Checks if the vehicle's height exceeds the matched max_height. If yes, it returns 1 (counts this vehicle), otherwise 0.
• SUM(...): Aggregates all the 1s and 0s per model group, giving you the total number of vehicles that exceed the height limit for each model.

Critical Notes to Avoid Issues #

• Match Model Field Formats: Double-check that the model fields in both tables ([VEHICLE].[model] and [HEIGHT_LIMIT].[model]) are identical—no typos, extra spaces, or case differences. Mismatches here will make the LOOKUP fail to find the correct limit.
• Handle Missing Limit Entries: If some models don't have a height limit in the HEIGHT_LIMIT table, the LOOKUP will return a null value. The current expression will ignore these vehicles (since comparing a number to null returns false). If you want to treat these as "no limit" (count all of them), adjust the LOOKUP to include a default value:

  LOOKUP([HEIGHT_LIMIT].[max_height], [HEIGHT_LIMIT].[model] = [VEHICLE].[model], 9999)
  

  Replace 9999 with a value higher than any possible vehicle height.
• Group by Model: Make sure your bar chart is grouped by the model field (use [VEHICLE].[model] to include all models present in the vehicle table, even those without limits).

内容的提问来源于stack exchange，提问作者txemsukr