i want to apply aggregate on infotable

[rkuppusamy]

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

Kindly help me on this

[CarlesColl]

Hi,

You just need to use InfoTable.Aggregate snippet.

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

    t: infotableVar,

    columns: "Count",

   aggregates: "SUM",

  groupByColumns: "ID,Hour"

});

[vsquaresystem]

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

[CarlesColl]

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

[vsquaresystem]

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

[CarlesColl]

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

Should be the column name: columns: "temp_min"

[vsquaresystem]

Yes, I tried but no luck!

[CarlesColl]

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

[vsquaresystem]

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

[CarlesColl]

But you are aggregating from result instead of result1

[vsquaresystem]

Yes, But i tried both result1 and result.

[CarlesColl]

Should work, would you send me the CSV file and I try it?

[vsquaresystem]

Yes, PFA of CSV..

Regards

Vikas

[CarlesColl]

Can you send also the DataShape please (export xml)

[CarlesColl]

Well on the meantime I was processing the file with another shape, and I didn't had any problem to get the minimum

var result = Resources["CSVParserFunctions"].ReadCSVFile( {
    path: "test_aggrigation.csv" /* STRING */,
    columnMappings: undefined,//"column1;column2;column3",
    hasHeader: false,
    longitudeField: undefined /* NUMBER */,
    dateFormat: undefined /* STRING */,
    fileRepository: "SystemRepository" /* THINGNAME */,
    latitudeField: undefined /* NUMBER */,
    fieldDelimiter: ",",
    stringDelimiter: undefined /* STRING */,
    dataShape: "aggrigateShape" /* DATASHAPENAME */
});

result = Resources["InfoTableFunctions"].Aggregate({
    t: result, // INFOTABLE
    columns: "column1", // STRING
    aggregates: "MIN", // STRING
    groupByColumns: undefined // STRING
}); 

Maybe the problem of your solution it's that you are setting a not created property --> me.temp_min did you previously created this property on the thing itself?

[vsquaresystem]

Brilliant, Thank you so much Carles for Perfect Solutions & Faster reply for me.

Best Regards

Vikas

[Harsha616]

Hi Vikas, I am in the same situation as you were before. I followed all the steps mentioned by Carles but couldn't resolve the issue can you please help me out on this?