Pivoting and advanced sorting

[xavalv]

Hello,

let's say I have a DataStream where I collect temperature and Humidity values from different sensors(=sources). Each sensor send data every 15 seconds, and sensors are asynchronous. 

The aim is to write a service in the DataStream (right table below)in order to have as output an infotable (colored left table below)which will be connected as entry to a timeSeriesChart that draws each temperature curves:

I've succeeded in sorting and then pivoting the temperature values (table in the middle) but I want to sort every source column with the corresponding  ascending timestamp (as I want to draw continuous curves, I need continuous rows for a given source without null values in the middle). Are there "advanced" sorting or joining techniques to achieve this? 

[nmilleson]

@xavalv ,

The query object lets you define complex sorts.  Then you can just run the query snippet: 

var params = {
	t: data /* INFOTABLE */,
	query: query /* QUERY */
};

try{
	// result: INFOTABLE
	var result = Resources["InfoTableFunctions"].Query(params);
}
catch(err)
{
    var result = data;
}

See here for info on the query object and complex sorts:

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx%2FHelp%2FComposer%2FThings%2FThingServices%2FQueryParameterforQueryServices.html

[xavalv]

thank you for you quick feedback. I've already tested sorting features but it gave ma erratic results

Both curves are continuous but the blue one is not coherent from a time perspective (here only with 2 sources):

[nmilleson]

@xavalv ,

Would you be able to share your query object and the data that is being returned to your chart?  On a related note, if you're using the Advanced Chart Widget, it will force null values to zero.  However, there is an easy way to modify that widget so it will just ignore null values (and then you wouldn't have to worry about the complex sorting).  If you'd like, I can share that with you.

[xavalv]

It could work with this king of script below (based on the temperature example at the beginning of the thread) where I generate an infotable from a DataStream, then do a Pivot foused on Temperature values with sources as colmuns

Then I generate 1 infotable for each source, and make an Union for the 3 infotables. But here it's done manually within the script. If the is another source in the DataStream how to cope automatically with that?

var query = {
"filters": {
"type": "GT",
"fieldName": "temperature",
"value": -1
}
};

var MaTable = me.QueryStreamEntriesWithData({
maxItems: maxItems /* NUMBER */,
endDate: DateDebut /* DATETIME */,
query: query /* QUERY */,
source: source /* STRING */,
startDate: DateFin /* DATETIME */
});

var params = {
t : MaTable,
nameColumn: "source",
valueColumn : "temperature",
timestampColumn :"timestamp"
};

var TablePivotee = Resources["InfoTableFunctions"].Pivot(params);

var query1 = {
"filters": {
"type": "GT",
"fieldName": "source1",
"value": -1
}
};
var params1 = {

t: TablePivotee /* INFOTABLE */,
query: query1
};

var query2 = {
"filters": {
"type": "GT",
"fieldName": "source2",
"value": -1
}
};
var params2 = {

t: TablePivotee /* INFOTABLE */,
query: query2
};

var query3 = {
"filters": {
"type": "GT",
"fieldName": "source3",
"value": -1
}
};
var params3 = {

t: TablePivotee /* INFOTABLE */,
query: query3
};
// result: INFOTABLE

var resultat1 = Resources["InfoTableFunctions"].Query(params1);
var resultat2 = Resources["InfoTableFunctions"].Query(params2);
var resultat3 = Resources["InfoTableFunctions"].Query(params3);

var paramunion = {
t1: resultat1 ,
t2: resultat2,
t3: resultat3
};
var result = Resources["InfoTableFunctions"].Union(paramunion);

[nmilleson]

@xavalv ,

Is all of this for the sole purpose of removing null values for charting?  If so, it may be easier just to modify your chart widget to not force those to zero.  If you need it for a grid, perhaps you can add another column to your pivot table that has an index, and then sort by that and timestamp.  For example, add a  column called sourceId to your pivot table and for source1 put a 1, source 2 put a 2, etc..

[slangley]

Hi @xavalv.

If one of the previous responses answered your question, please mark the appropriate one as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon