# Filtering a dataset and calculations

**URL:** <https://forum.inductiveautomation.com/t/filtering-a-dataset-and-calculations/9768>\
**Category:** Ignition\
**Created:** [March 3, 2015, 7:33pm UTC](https://forum.inductiveautomation.com/t/filtering-a-dataset-and-calculations/9768 "2015-03-03T19:33:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\
**Post date:** [March 3, 2015, 7:33pm UTC](https://forum.inductiveautomation.com/t/filtering-a-dataset-and-calculations/9768/1 "2015-03-03T19:33:47Z")

</div>

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

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/274e56398b8e6b1b42d26aea5931bc5d012ee1ed.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 3, 2015, 9:18pm UTC](https://forum.inductiveautomation.com/t/filtering-a-dataset-and-calculations/9768/2 "2015-03-03T21:18:09Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/ab7645a16457d15e3f3f7f98a2f29fa4155a41c0.proj) (12.4 KB)