Query and Group By

[rtaylor-3]

Greetings,

I have a requirement for storing some data in a Stream and then providing a service to query that Stream and aggregate the results by certain value columns.

For example, if i had a stream backed by a DataShape containing ID, NumericValue columns, and i wanted to rollup the numeric values to the beginning of each month (using the Stream Timestamp), is this even possible using the Query/filtering API?

[paic · Accepted solution]

Use Query to get your slice of data and then use Aggregate (InfoTable Functions) to get the roll up.

[rtaylor-3]

Thanks Pai. I'll give it a try.

[rtaylor-3]

Yep....worked. Thanks.

[TricoSupport]

Hi Pai...still new to Thingworx (using 7.0).  Could you explain this further?  I have MSSQL query with a large amount of data and I would like to group it in a graph based on a specific field in that data set.

The query is set up as an infotable

Thanks, Charlie

[PaiChung]

Hi Charlie,

if you are querying MSSQL server, I would actually write a stored procedure or write a query, that delivers the data grouped and aggregated as desired.

i.e. the MSSQL Server is doing the heavy lifting before sending it to Thingworx.

Then in Thingworx you just assign the data that comes in to a Graph.

If you had to do it in Thingworx, then you would get the 'raw' dataset first from MSSQL server and use the InfoTable functions to do your aggregate.

[TricoSupport]

Thank you very much, Pai.

I will do my grouping in MSSQL