Skip to main content
S
SSNX1-Visitor
1-Visitor
March 28, 2019
Solved

Mashup - filter/average on datatable

  • March 28, 2019
  • 2 replies
  • 1946 views

Hi,

 

I have a datatable where I store information based on location.

 

Like this: 

Department | Name | Ratio1 | Ratio2 | Ratio3 | Ration4

Dept1             N01      90           85         85        90

Dept1             N02      92           87         87        92

Dept2             N07      90           85         85        88

Dept2             N18      90           85         85        88

Dept2             N22      90           85         85        88

Dept3             N10      84           72         85        90

 

I would like to display on a mashup several "Dispaly Value" with Department average for "Ratio".

 

Value Dept1, Ratio1 = 91

Value Dept1, Ratio1 = 90

Value Dept3, Ratio1 = 84

 

How would you proceed ?

 

Thank you

Best answer by SSNX

Hi,

 

Finally, I have created a DataStore and I have a timer doing the calculation, so I have 1 line per deparmtent.

 

 

var dtParams = { maxItems: undefined /* NUMBER */ };
var dtValues = Things["DataTableMachinesAllProdData"].GetDataTableEntries(dtParams); //INFOTABLE


var dtMaxParams = {
t: dtValues, // INFOTABLE
columns: "Ratio1,Ratio2,Ratio3,Ratio4", // STRING
aggregates: "AVERAGE,AVERAGE,AVERAGE,AVERAGE", // STRING
groupByColumns: "Department" // STRING
};
var n = Resources["InfoTableFunctions"].Aggregate(dtMaxParams); 

var values = Things["DataTableDepartmentOEEShift"].CreateValues();

 Things["DataTableDepartmentOEEShift"].PurgeDataTableEntries(); // We empty the DataTable. In case we remove/rename a department 
// location:LOCATION
var location = new Object();
location.latitude = 0;
location.longitude = 0;
location.elevation = 0;
location.units ="WGS84";
var tags = new Array();
var paramsDataTable = {
 tags : tags,
 source : me.name,
 values : values,
 location : location
};

for(var i=0 ; i<n.getRowCount() ; i++) {
	values.Department = n[i].Department;
	values.Ratio1= Number(n[i].AVERAGE_Ratio1);
	values.Ratio2= Number(n[i].AVERAGE_Ratio2);
	values.Ratio3= Number(n[i].AVERAGE_Ratio3);
	values.Ratio4= Number(n[i].AVERAGE_Ratio4);
 Things["DataTableDepartmentOEEShift"].AddOrUpdateDataTableEntry(paramsDataTable);
}

 

 

I can easily query DataTable in a grid on my mashups.

 

Thanks for your help

2 replies

W
wposner-21-Visitor
1-Visitor
March 28, 2019

Create a datashape with the columns you want to display.  In a new service, create a new instance of an infotable that uses this datashape.  Loop over the data in your data table and do the math to create your averages.  Add rows to your infotable.  Bind the result of your service to a datagrid.

S
SSNX1-VisitorAuthor
1-Visitor
March 28, 2019

Thank you for your answer.

How do you do Math ? Sorry for stupid question but I am just starting for a week only...

W
wposner-21-Visitor
1-Visitor
March 28, 2019

Based on your example, you have 8 data points for Dept 1 (two rows with 4 columns of data).  Because you have data spread across multiple rows for the same dept, you should consider using a key value pair object where the value is an array; preprocess your datatable i to your KVP object, adding all the values for dept1 to the dep1 row in your KVP object.   Then you can loop over your KVP object and correctly add all the values together for each key and divide by the length of the array.  As you're looping over your array you can add the correct values to your infotable.

S
SSNX1-VisitorAuthorAnswer
1-Visitor
April 1, 2019

Hi,

 

Finally, I have created a DataStore and I have a timer doing the calculation, so I have 1 line per deparmtent.

 

 

var dtParams = { maxItems: undefined /* NUMBER */ };
var dtValues = Things["DataTableMachinesAllProdData"].GetDataTableEntries(dtParams); //INFOTABLE


var dtMaxParams = {
t: dtValues, // INFOTABLE
columns: "Ratio1,Ratio2,Ratio3,Ratio4", // STRING
aggregates: "AVERAGE,AVERAGE,AVERAGE,AVERAGE", // STRING
groupByColumns: "Department" // STRING
};
var n = Resources["InfoTableFunctions"].Aggregate(dtMaxParams); 

var values = Things["DataTableDepartmentOEEShift"].CreateValues();

 Things["DataTableDepartmentOEEShift"].PurgeDataTableEntries(); // We empty the DataTable. In case we remove/rename a department 
// location:LOCATION
var location = new Object();
location.latitude = 0;
location.longitude = 0;
location.elevation = 0;
location.units ="WGS84";
var tags = new Array();
var paramsDataTable = {
 tags : tags,
 source : me.name,
 values : values,
 location : location
};

for(var i=0 ; i<n.getRowCount() ; i++) {
	values.Department = n[i].Department;
	values.Ratio1= Number(n[i].AVERAGE_Ratio1);
	values.Ratio2= Number(n[i].AVERAGE_Ratio2);
	values.Ratio3= Number(n[i].AVERAGE_Ratio3);
	values.Ratio4= Number(n[i].AVERAGE_Ratio4);
 Things["DataTableDepartmentOEEShift"].AddOrUpdateDataTableEntry(paramsDataTable);
}

 

 

I can easily query DataTable in a grid on my mashups.

 

Thanks for your help

Terms & ConditionsAccessibility statement