Help inserting rows of totals to a dataset from a query

Ignition
12

Assuming your query will return something with ORDER BY Timestamp, Shift, Line to give data as in your original post. I would even go so far as to use just the date portion of Timestamp. Then, you are just looking for changes in either the date or the shift.

sampleHeaders = ['Line', 'Shift', 'Hours', 'Gross', 'Net', 'Timestamp']
sampleData = [['K01', 1, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
              ['K02', 1, 12, 202, 152, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
              ['K03', 1, 12, 203, 153, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
              ['K01', 2, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
              ['K02', 2, 12, 208, 155, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
              ['K03', 2, 12, 209, 157, system.date.parse('2022-09-25', 'yyyy-MM-DD')]
             ]

datasetIn = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, sampleData))

#util.printDataSet(datasetIn)
#print ''

headers = list(datasetIn.getColumnNames())
dataOut = []

grossSum = netSum = 0
for row, nextRow in zip(datasetIn, datasetIn[1:]):
	# Add values for the row
	grossSum += row['Gross']
	netSum += row['Net']
	# Append the row into the output data
	dataOut.append(list(row))
	# Compare the timestamp and shift values to the next row.
	# If they're different, append the total row to the output data.
	if row['Timestamp'] != nextRow['Timestamp'] or row['Shift'] != nextRow['Shift']:
		dataOut.append(['Total', None, None, grossSum, netSum, None]) 
		grossSum=0
		netSum = 0
# Process the final row
grossSum += nextRow['Gross']
netSum += nextRow['Net']
dataOut.append(list(nextRow))
dataOut.append(['Total', None, None, grossSum, netSum, None]) 

# Create the dataset
datasetOut = system.dataset.toDataSet(headers, dataOut)

#util.printDataSet(datasetOut)

Output:

row | Line | Shift | Hours | Gross | Net | Timestamp                   
-----------------------------------------------------------------------
0   | K01  | 1     | 12    | 200   | 150 | Tue Jan 25 00:00:00 EST 2022
1   | K02  | 1     | 12    | 202   | 152 | Tue Jan 25 00:00:00 EST 2022
2   | K03  | 1     | 12    | 203   | 153 | Tue Jan 25 00:00:00 EST 2022
3   | K01  | 2     | 12    | 200   | 150 | Tue Jan 25 00:00:00 EST 2022
4   | K02  | 2     | 12    | 208   | 155 | Tue Jan 25 00:00:00 EST 2022
5   | K03  | 2     | 12    | 209   | 157 | Tue Jan 25 00:00:00 EST 2022

row | Line  | Shift | Hours | Gross | Net | Timestamp                   
------------------------------------------------------------------------
0   | K01   | 1     | 12    | 200   | 150 | Tue Jan 25 00:00:00 EST 2022
1   | K02   | 1     | 12    | 202   | 152 | Tue Jan 25 00:00:00 EST 2022
2   | K03   | 1     | 12    | 203   | 153 | Tue Jan 25 00:00:00 EST 2022
3   | Total | None  | None  | 605   | 455 | None                        
4   | K01   | 2     | 12    | 200   | 150 | Tue Jan 25 00:00:00 EST 2022
5   | K02   | 2     | 12    | 208   | 155 | Tue Jan 25 00:00:00 EST 2022
6   | K03   | 2     | 12    | 209   | 157 | Tue Jan 25 00:00:00 EST 2022
7   | Total | None  | None  | 617   | 462 | None
3 Likes