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

i want to apply aggregate on infotable

Newbie

i want to apply aggregate on infotable

existing table.png

The above is the existing info table now i need to group by based upon the Hour as shown below

Required data.png

Kindly help me on this

16 REPLIES 16

Re: i want to apply aggregate on infotable

Hi,

You just need to use InfoTable.Aggregate snippet.

var result = Resources["InfoTableFunctions"].Aggregate({

    t: infotableVar,

    columns: "Count",

   aggregates: "SUM",

  groupByColumns: "ID,Hour"

});

Re: i want to apply aggregate on infotable

Hi,

 

I am unable to use aggregation for sum or max or min value calculations.

 

Here is my code: 


var params = {
path: "test_aggrigation.csv" /* STRING */,
columnMappings: undefined /* STRING */,
hasHeader: undefined /* BOOLEAN */,
longitudeField: undefined /* NUMBER */,
dateFormat: undefined /* STRING */,
fileRepository: "FileStorageThing" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: undefined /* STRING */,
stringDelimiter: undefined /* STRING */,
dataShape: "aggrigateShape" /* DATASHAPENAME */
};

// result: INFOTABLE
var data = Resources["CSVParserFunctions"].ReadCSVFile(params);

logger.warn(data);

 

//Aggregate code start from here..

var result = Resources["InfoTableFunctions"].Aggregate({
t: data,
columns: "temp_min",
aggregates: "SUM",
groupByColumns: "temp_max,date"
});

 

ERROR Code:

Error executing service:

Error executing service calculateMin. Message :: java.lang.String cannot be cast to java.lang.Number - See Script Error Log for more details.

 

Can you please help me on this?

 

Regards

Vikas

 

 

Re: i want to apply aggregate on infotable

temp_min column it's a String or a Number?

 

But anyway, ReadCSVFunctions shouldn't work, as you are missing some required parameters like columnMappings, focus first on reading correctly the csv file, and the temp_min columns as a Number and then aggregate

Re: i want to apply aggregate on infotable

Hi Carles,

 

I am able to read csv file with above code. "temp_min" is number!

 

HERE is my code:
var params = {
path: "test_aggrigation.csv" /* STRING */,
columnMappings: undefined /* STRING */,
hasHeader: "false" /* BOOLEAN */,
longitudeField: undefined /* NUMBER */,
dateFormat: undefined /* STRING */,
fileRepository: "FileStorageThing" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: undefined /* STRING */,
stringDelimiter: undefined /* STRING */,
dataShape: "aggrigateShape" /* DATASHAPENAME */
};

result1 = Resources["CSVParserFunctions"].ReadCSVFile(params);

var params1 = {
maxItems: undefined /* NUMBER */
};

// result: INFOTABLE
var result = Things["TestDataTable"].GetDataTableEntries(params1);

var newID=0 // NUMBER
var dtMaxParams = {
t: result, // INFOTABLE
columns:"'"+result.temp_min+"'", // STRING
aggregates: "MIN", // STRING
groupByColumns: undefined // STRING
};

var dtMax = Resources["InfoTableFunctions"].Aggregate(dtMaxParams); //INFOTABLE
//logger.warn(dtMax);
me.temp_min = dtMax.MIN_temp_min;

 

 

Finally, i can see me.temp_min is undefined!

 

Can you help me on this please?

 

Regards

Vikas

 

Re: i want to apply aggregate on infotable

This parameter: columns:"'"+result.temp_min+"'", // STRING

Should be the column name: columns: "temp_min"

Re: i want to apply aggregate on infotable

Yes, I tried but no luck!

Re: i want to apply aggregate on infotable

Your previous code tries to aggregate from the DataTable not from the CSV, it's what you want to do?

Re: i want to apply aggregate on infotable

I am getting data from csv and want to aggregate from there..

Re: i want to apply aggregate on infotable

But you are aggregating from result instead of result1