Ran into this recently thought I share an approach to getting a table with multi-column distinct yet retaining all the columns of the row.
If you use Distinct, you get only the Columns you do Distinct on.
This isn't very helpful if you want the 'latest' or the 'first occurrences' of records in your table with a combination of fields being unique.
For example I had Process, Part, Dimension and Point for which I had multiple value and date time entries, but I only wanted the latest entries.
Following is how I solved it, if you have a better way please leave a comment!
P.S.: for the query I used the awesome query builder available in the snippet section!
---------------------------------------
var q1Result = Things["MyThing"].QueryStreamEntriesWithData({maxItems:99999, query:query1});
//Below creates a temporary measurement table to store the latest meaurement values
var params = {
infoTableName : "InfoTable",
dataShapeName : "MyDatashape.DS"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(MyDataShape.DS)
var tempTable1 = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
// Extract only the latest measurements for the PART from the measurement result table 'q1Result'
//The way we are going to reduce this to unique measurements is
//1. records are in reverse order of date time
//2. get distinct by Process Part Dim Point
//3. Step through and match against distinct set
//4. First match goes into final set
//5. Upon match remove from distinct set
//6. If no match then skip record
//7. If no more distinct match records break loop
var params = {
t: q1Result /* INFOTABLE */,
columns: 'ProcessID,PartID,Dimension,Point' /* STRING */
};
// result: INFOTABLE
var distinctResult = Resources["InfoTableFunctions"].Distinct(params);
for (var x = 0; x < q1Result.rows.length; x++) {
var query = {
"filters": {
"type": "AND",
"filters": [
{
"fieldName": "ProcessID",
"type": "EQ",
"value": q1Result.rows.ProcessID
},
{
"fieldName": "PartID",
"type": "EQ",
"value": q1Result.rows.PartID
},
{
"fieldName": "Dimension",
"type": "EQ",
"value": q1Result.rows.Dimension
},
{
"fieldName": "Point",
"type": "EQ",
"value": q1Result.rows.Point
}
]
}
};
var params = {
t: distinctResult /* INFOTABLE */,
query: query /* QUERY */
};
// result: INFOTABLE
var matchResult = Resources["InfoTableFunctions"].Query(params);
if (matchResult.rows.length == 1) {
tempTable1.AddRow(q1Result.rows);
var params = {
t: distinctResult /* INFOTABLE */,
query: query /* QUERY */
};
// result: INFOTABLE
var distinctResult = Resources["InfoTableFunctions"].DeleteQuery(params);
if (distinctResult.rows.length == 0) {
break
}
}
}
//I now have a tempTable1 with the full rows and the 4 fields distinct
result = tempTable1
