cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Mashup - filter/average on datatable

SOLVED
Highlighted
Regular Member

Mashup - filter/average on datatable

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Mashup - filter/average on datatable

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

View solution in original post

4 REPLIES 4

Re: Mashup - filter/average on datatable

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.

Re: Mashup - filter/average on datatable

Thank you for your answer.

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

Re: Mashup - filter/average on datatable

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.

Re: Mashup - filter/average on datatable

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

View solution in original post