Do Spotfire's "min aggregation" and "marked by" correspond to any SQL statements?


Keywords:sql 


Question: 

Say, I have a table with three columns and two rows:

x      y      my_type
---------------------
4      0      a
2      1      a
1      0      b

Here is what I'm trying to accomplish: in each of my_type, return a row with the minimal x coordinate.

The following SQL query

select min(x), y, my_type
from mydata
group by my_type

would give me this as a result:

x      y      my_type
---------------------
2      1      a
1      0      b

So I though that setting Spotfire's scatter plot axes to min(x) and y, and choosing marked by my_type would plot only this a-type point (2,1). But it plots both a-type points (4, 0) and (2, 1).

Two questions:

  1. How do I make it plot only (2, 1)?
  2. In general, do Spotfire's "min aggregation" and "marked by" correspond to any SQL statements?

3 Answers: 

Spotfire is not good at managing your data; in fact it's pretty terrible at it. Spotfire instead generally expects you to have the data set figured out before it starts to visualize. one of the other answers above may help you with that on the database level.

assuming you're using an Information Link, you can of course modify the SQL statement powering said link in accordance with one of the answers, and that can be a solution.

in short: if your data set has 3 rows, 3 rows will be represented in the visualization unless some of those rows have been explicitly filtered out.

but to answer your questions:

How do I make it plot only (2, 1)?

you will need to either remove the other values you do not wish to plot from the data set, OR filter out those specific values using one of the various filtering mechanisms in the application (Filters Panel, Data Limiting Expression, etc.).

In general, do Spotfire's "min aggregation" and "marked by" correspond to any SQL statements?

I refer you to the Marker By documentation from the Spotfire manual:

If you select (Row Number) here, then each row in the data table will be displayed as a separate marker in the visualization, even if several markers have identical values for the X-axis and Y-axis, and therefore are located at the exact same coordinates. These markers will be drawn on top of each other.

You can, however, select to combine markers (known as aggregation) so that each marker shows for example the sum of all rows that have something in common.

Marker By is a visualization dimension, similar to X Axis or Color By. it has no similar function or statement in any SQL language as it's not in the same wheelhouse (or wheel..city even).

the Min() aggregation however should function exactly as you'd expect it to in SQL, i.e., it will return the minimum value contained in the column.



I believe you want the following query

select *
from mydata m
where m.x = 
  (
    select min(x) min_x
    from mydata
  )


DECLARE @TestTable AS TABLE (
    X INT,
    Y INT,
    my_type NVARCHAR(2)
)

INSERT INTO @TestTable
VALUES
     (4, 0, 'a')
    ,(2, 1, 'a')
    ,(1, 0, 'b')

;WITH MinimumXGroupedByType
AS
(
    SELECT 
        MIN(X) AS min_x
        ,my_type
    FROM @TestTable
    GROUP BY my_type
)

SELECT T.* FROM @TestTable AS T
    INNER JOIN MinimumXGroupedByType AS M ON [M].[my_type] = T.[my_type] AND [M].[min_x] = T.[X]