How to combine two InfoTables via a Service

[#1]

Let's assume I collect Timeseries Data of two temperature sensors, located next to each other. This is done for redundancy and ensuring the quality of measures. Each of the sensors is logged into its Property in ThingWorx and I can create a Timeseries for the individual sensors. However I would like to create a combined InfoTable that holds information for both sensors, but averages out their values.

Instead of reading values from a stream, I just create some custom data for both InfoTables. After this I use the UNION function to combine the two tables and sort them. Once they are sorted, the INTERPOLATE function allows to group the InfoTable by timestamp.

With this, I have combined the two sensor result into on result set. Taking the average of numbers will give closer results to the real value (as both sensors might not be 100% accurate). In case one sensor does not have data for a given point in time, it will still be considered in the final output.

InfoTable1:

2018-12-18 00:00:00.000	2
2018-12-19 00:00:00.000	3
2018-12-20 00:00:00.000	5
2018-12-21 00:00:00.000	7

InfoTable2:

2018-12-18 00:00:00.000	1
2018-12-19 12:00:00.000	2
2018-12-20 00:00:00.000	3
2018-12-21 00:00:00.000	4

Combined Result:

2018-12-18 00:00:00.000	1.5
2018-12-19 00:00:00.000	3
2018-12-19 12:00:00.000	2
2018-12-20 00:00:00.000	4
2018-12-21 00:00:00.000	5.5

This can be done with the following code:

// Required DataShape "myInfoTableShape": "timestamp" = DATETIME, "value" = NUMBER
// The Service Output is an InfoTable based on the same DataShape

var params = {
    infoTableName : "InfoTable",
    dataShapeName : "myInfoTableShape"
};

// Create two InfoTables, representing the data of each sensor

var infoTable1 = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
var infoTable2 = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var newEntry = new Object();

// Create custom data for InfoTable1

newEntry.timestamp = 1545091200000; newEntry.value = 2; infoTable1.AddRow(newEntry);
newEntry.timestamp = 1545177600000; newEntry.value = 3; infoTable1.AddRow(newEntry);
newEntry.timestamp = 1545264000000; newEntry.value = 5; infoTable1.AddRow(newEntry);
newEntry.timestamp = 1545350400000; newEntry.value = 7; infoTable1.AddRow(newEntry);

// Create custom data for InfoTable2

newEntry.timestamp = 1545091200000; newEntry.value = 1; infoTable2.AddRow(newEntry);
newEntry.timestamp = 1545220800000; newEntry.value = 2; infoTable2.AddRow(newEntry);
newEntry.timestamp = 1545264000000; newEntry.value = 3; infoTable2.AddRow(newEntry);
newEntry.timestamp = 1545350400000; newEntry.value = 4; infoTable2.AddRow(newEntry);

// Combine the two InfoTables via the UNION function

var unionTable = Resources["InfoTableFunctions"].Union({
	t1: infoTable1,
	t2: infoTable2
});

// Optional: Sort the table by timestamp

var sortedTable = Resources["InfoTableFunctions"].Sort({
	sortColumn: "timestamp",
	t: unionTable,
	ascending: true
});

// Interpolate the (sorted) table by Interval and take average values and build the result

var result = Resources["InfoTableFunctions"].Interpolate({
	mode: "INTERVAL",
	timeColumn: "timestamp",
	t: sortedTable,
	ignoreMissingData: undefined,
	stats: "AVG",
	endDate: 1545609600000,
	columns: "value",
	count: undefined,
	startDate: 1545004800000
});