SQL to spotfire question

[Anthony stark]

Hi Guys,

Im trying to build this squery as an spotifre expression:

SELECT COUNT (CRAFT) AS COUNT, WORKID

FROM LABOR

GROUP BY WORKID

HAVING COUNT>1

How do i translate this to a spotfire expression Thanks in advance

[Colin Gray 3]

Hithere,

 

For the equivalent of group by expressions you normally use OVER statements in Spotfire (although many visuals such as cross tables will do this on the fly for you). The help on OVER statementshas some good examples if you search for OVER in the help and take a look at 'in advanced custom expressions', 'in calculated columns' and 'in custom expressions'.

 

For you example, you could do something like:

 

Count([CRAFT]) Over ([WORKID])

 

Which should mimic the SQL statement you have.

 

You can wrap this in an IF statement perphaps or add it as a data limiting expression on a chart to mimic the HAVING expression. So the expression as a filter to your chart (by adding limited data by expression from the Data option):

 

Count([CRAFT]) Over ([WORKID]) > 1

 

If you want to wrap it in an IF you could do:

 

IF(Count([CRAFT]) Over ([WORKID]) > 1,Count([CRAFT]) Over ([WORKID]), NULL).

 

Or create it as a calculated column in your data table then you can simple do:

 

IF([My New Column]>1, [My New Column], NULL)

 

Hope this helps,

Colin

[Anthony stark]

Thanks Colin!