Simple loop with columns

[Michael_Dezet]

I'm having trouble writing a simple loop. I have arrays with three columns.
The columns are Group , Date and Value. The group is the same for all rows.

The same date occurs many times - and I need to sum up the values for the same dates and write them into a new table.

The code I can write in pyhon and VBA - here I can not get around the limitations of JS in ThingWorx

[Velkumar · Accepted solution]

Hi @Michael_Dezet 

Considering Array means InfoTable on the Thingworx side

I have sample dummy data

Below code to calculate each day sales

var rawData = me.dummy;

// Create Infotable to store data
let params3 = {
	infoTableName: undefined /* STRING {"defaultValue":"InfoTable"} */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].CreateInfoTable(params3);

// Add fields to store total and date value
result.AddField({
	"name": "Total",
	"baseType": "INTEGER"
});
result.AddField({
	"name": "Date",
	"baseType": "DATETIME"
});

// Get Distinct / Unique Value of Date. Removes duplicate dates from Infotable
// Used to get count for each day
let params = {
	t: rawData /* INFOTABLE */ ,
	columns: "Date" /* STRING */
};

// result: INFOTABLE
let distinctDateValues = Resources["InfoTableFunctions"].Distinct(params);


// Iterate Over distinct date values
for (let i = 0; i < distinctDateValues.length; i++) {

	// Create query to filter data based on date 
	let query = {
		"filters": {
			"type": "EQ",
			"fieldName": "Date",
			"value": distinctDateValues[i]["Date"] // Date is keyword in JS. Always add suffix / prefix to date variable
		}
	};

	let params = {
		t: rawData /* INFOTABLE */ ,
		query: query /* QUERY */
	};

	// result: INFOTABLE
	var filteredValue = Resources["InfoTableFunctions"].Query(params);

	// Calculate sum of each day
	let params2 = {
		t: filteredValue /* INFOTABLE */ ,
		columns: "Value" /* STRING */ ,
		aggregates: "SUM" /* STRING */ ,
		groupByColumns: undefined /* STRING */
	};

	// result: INFOTABLE
	var totalForDay = Resources["InfoTableFunctions"].Aggregate(params2);

	// Add calculated value to new infotable
	result.AddRow({
		"Total": totalForDay.SUM_Value,
		"Date": distinctDateValues[i]["Date"]
	});

}

// Additional : sort column by date

let params4 = {
	sortColumn: "Date" /* STRING */,
	t: result /* INFOTABLE */,
	ascending: false /* BOOLEAN {"defaultValue":true} */
};

// result: INFOTABLE
result = Resources["InfoTableFunctions"].Sort(params4);

Result :

Or 

If you want the total directly from Infotable, just mention groupByColumns as "Date" 

// Calculate sum of each day
let params2 = {
	t: me.dummy /* INFOTABLE */ ,
	columns: "Value" /* STRING */ ,
	aggregates: "SUM" /* STRING */ ,
	groupByColumns: "Date" /* STRING */
};

// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Aggregate(params2);

Please do post here if you have any queries 

/VR

[Arun_C]

Hi @Michael_Dezet , 

As continue of @Velkumar post, If you are considering direct array in your case means. Please find the below code,

var inputArray = [
  { Group: 'A', Date: '2023-08-29', Value: 10 },
  { Group: 'A', Date: '2023-08-29', Value: 20 },
  { Group: 'A', Date: '2023-08-30', Value: 15 },
  { Group: 'A', Date: '2023-08-30', Value: 25 },
  { Group: 'A', Date: '2023-09-30', Value: 25 },
];

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(PTC.SimpleLoop.DS)
let summedTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
    infoTableName: "InfoTable",
    dataShapeName: "PTC.SimpleLoop.DS"
});

var summedValues = {};

for (var i = 0; i < inputArray.length; i++) {
  var row = inputArray[i];
  var group = row.Group;
  var date = row.Date;
  var value = row.Value;

  var key = group + "_" + date;

  if (!summedValues[key]) {
    summedValues[key] = { Group: group, Date: date, Value: value };
  } else {
    summedValues[key].Value += value;
  }
}
for (var key in summedValues) {
	var summedRow = summedValues[key];
	// PTC.SimpleLoop.DS entry object
	let newEntry = {
		Group: summedRow.Group, // STRING
		Date: summedRow.Date, // DATETIME
		Value: summedRow.Value // NUMBER
	};
	summedTable.AddRow(summedRow);
}
result = summedTable;

Output as below

Thanks & Regards,

Arun C