Community Tip - You can change your system assigned username to something more personal in your community settings. X
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.
Solved! Go to 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
The Aggregate service can give you MAX (as well as MIN COUNT AVERAGE SUM)
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 = ????
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
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!
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 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