How to return MAX() from a specific field in Data Table?

[chrish]

I'm trying to create a service that I can globally put in a ThingShape; then reference inside every new DataTable that I create. This service will find the PK of each new data table and ensure that it is populated with a unique/increasing value. To do this, I need to be able to find the current MAX() value of all data in the data table for the PK field; then set new row PK field to MAX()+1.

If I'm simply way out in left field...please reign me in, but I think I'm close.

Please provide a sample script for how to query this information from an existing data table.

[PaiChung]

The Aggregate service can give you MAX (as well as MIN COUNT AVERAGE SUM)

[chrish]

Ok, almost there!  dtMax variable is a INFOTABLE with exactly 1 row and 1 column. How do I set the variable "newID" to the single numeric value of dtMax?

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

    var dtValues = me.GetDataTableEntries(dtParams); //INFOTABLE

    var newID=0 // NUMBER

    var dtMaxParams = {

        t: dtValues, // INFOTABLE

        columns: KEY_Field, // STRING

        aggregates: "MAX", // STRING

        groupByColumns: undefined // STRING

      };

    var dtMax = Resources["InfoTableFunctions"].Aggregate(dtMaxParams); //INFOTABLE

    newID = ????

[PaiChung · Accepted solution]

So to find out what Aggregate puts out, you can always first set the result to the Aggregate (basetype infotable) and see the result in Composer.

Aggregate pre-pends the operation before the field it is acting upon.

So newID = dtMax.MAX_KEY_Field

[chrish]

Thanks Pai! I combined this with the service I referenced in:

How to use variable in "VALUES.<COLUMN>" as the <COLUMN>? (i.e. VALUES.variable=1)

I've built a really cool ThingShape to implement on all new DataTables that is really going to streamline maintenance.  Thanks again!

[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