I have a query that pulls a dataset of hourly milled rice based off a where clause for a selected “Lot”. The data set returned consist of DateTime, Lot, Grain, AhpBrwn. I would like to break this dataset down further by “Grain” and calculate time milled based off Start/Stop time and total amount milled for each grain. I don’t know what “Grain” I will have in each lot and can have up to 8 different grains per lot. Looking for ideas on the best way to go about doing this.
Dataset.png
You can sort the dataset by grain through scripting. Then iterate through the sorted dataset to count hours and sum the amount.
EDIT: Sorry for the the typos. I spelled the column as ‘AphBrwn’ instead of ‘AhpBrwn’… 
[code]headersIn=event.source.data.getColumnNames()
dataIn=system.dataset.toPyDataSet(system.dataset.sort(event.source.data,“Grain”))
headersOut=[‘Grain’, ‘Milling Time’, ‘Amount Milled’]
dataOut=
grain=dataIn[0][“Grain”]
millingTime=amountMilled=0
for row in dataIn:
if row[“Grain”] != grain:
dataOut.append([grain, millingTime, amountMilled])
millingTime=amountMilled=0
grain=row[“Grain”]
millingTime+=1
amountMilled+=row[“AphBrwn”]
dataOut.append([grain, millingTime, amountMilled])
event.source.parent.getComponent(‘Table 1’).data=system.dataset.toDataSet(headersOut,dataOut)
[/code]
A window to play with:
Sort and Count_2015-03-03_1608.proj (12.4 KB)
