Mashup - filter/average on datatable

[SSNX]

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

[wposner-2]

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.

[SSNX]

Thank you for your answer.

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

[wposner-2]

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.

[SSNX · Accepted solution]

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