# Combining datasets

**URL:** <https://forum.inductiveautomation.com/t/combining-datasets/9818>\
**Category:** Ignition\
**Created:** [March 19, 2015, 2:12pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818 "2015-03-19T14:12:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [March 19, 2015, 2:12pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/1 "2015-03-19T14:12:49Z")

</div>

I have a dataset of shift strings from my window.  
I have 2 popup calendars for start date and end date.  
I'm trying to create a dataset that looks like this to setup child groups broken out by date and shift.

Date Shift Shiftdataset  
10/5/2015 Shift1 [shiftdataset]  
10/5/2015 Shift2 [shiftdataset]  
10/6/2015 Shift1 [shiftdataset]  
10/6/2015 Shift2 [shiftdataset]

I can't seem to get past the date artithmatic, and i've tried datetime.timedelta and regular string formatting, nothing seems to work for me.  
button code:

```auto
from datetime import datetime
#create a dataset within a dataset
date1 = event.source.parent.getComponent('Popup Calendar').date
date2 = event.source.parent.getComponent('Popup Calendar 1').date
start = datetime.strptime(date1, '%YYYY-%m-%d')
end = datetime.strptime(date2, '%YYYY-%m-%d')
#set up the final data variables
finalHeader = ["Date", "Shift"]
finalData = []
diffTime = (end-start).days
dateList = []
while start <= end:
	dateList.append(start)
	start += step
#Select is a dataset with one column
#create a dataset with column of dates between date1 and date2 AND the Shift column
shifts = event.source.parent.Select
dateData = [dates, shifts]
event.source.parent.getComponent('Table 2').data = system.dataset.toDataSet(finalHeader, finalData)
```

I'm not concered about the shiftdata query yet.  
ignition version 7.6.6  
Any suggestions?

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [March 19, 2015, 2:47pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/2 "2015-03-19T14:47:21Z")

</div>

I want to get a list of the dates like this

`delta = datetime.timedelta(days=1)
while startDate <= endDate:
    event.source.parent.getComponent('Label').text = startDate.strftime("%Y-%m-%d")
    startDate += delta`

I’m going to try to convert to strings instead.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 19, 2015, 4:58pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/3 "2015-03-19T16:58:29Z")

</div>

There be dragons!

Timezones and daylight savings time will eat your lunch whenever you try this sort of thing. Fortunately, java has the Calendar class that will take care of such things. Your event script would look like this:

[code]from java.util import Calendar  
shift1hour = 6  
shift2hour = 14

cal = Calendar.getInstance()  
cal.time = event.source.parent.getComponent(“Popup Calendar”).date

date2 = event.source.parent.getComponent(“Popup Calendar 1”).date

rows =   
while cal.time.before(date2):  
cal.set(Calendar.HOUR\_OF\_DAY, shift1hour)  
rows.append([cal.getTime(), “1”])  
cal.set(Calendar.HOUR\_OF\_DAY, shift2hour)  
rows.append([cal.getTime(), “2”])  
cal.add(Calendar.DAY\_OF\_MONTH, 1)

table = event.source.parent.getComponent(“Table”)  
table.data = system.dataset.toDataSet([‘start’, ‘shift’], rows)  
[/code]

The “start” column may be all you need for your queries. Or you could add an “end” column to bracket the shift timeframe.  
[Shifts\_2015-03-19.proj](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d5195116f60ec5d48c4ab7097b8a4c63dfa26ea7.proj) (15.6 KB)

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [March 19, 2015, 5:33pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/4 "2015-03-19T17:33:35Z")

</div>

Thanks!  
Trying to open your window i get this error though,

SerializationException: Unexpected parsing error during binary deserialization.  
caused by IOExceptionWithCause: java.lang.ClassNotFoundException: com.inductiveautomation.ignition.common.script.ScriptScopeStyle  
caused by ClassNotFoundException: com.inductiveautomation.ignition.common.script.ScriptScopeStyle

Ignition v7.6.6 (b2014040112)  
Java: Oracle Corporation 1.7.0\_76

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 19, 2015, 5:45pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/5 "2015-03-19T17:45:45Z")

</div>

Sorry, I’m on 7.7.2. Project files aren’t backward compatible.

But just add a table named “Table” the window you started with and it should “just work”.

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [March 19, 2015, 6:42pm UTC](https://forum.inductiveautomation.com/t/combining-datasets/9818/6 "2015-03-19T18:42:08Z")

</div>

I got what I was looking for, thanks again for the calendar idea!  
4 fields Date, Shift, Start, End

here’s my code.

[code]from datetime import datetime  
from java.util import Calendar

#create a dataset within a dataset  
cal = Calendar.getInstance()  
cal.time = event.source.parent.getComponent(‘Popup Calendar’).date  
date2 = event.source.parent.getComponent(‘Popup Calendar 1’).date  
startDate = system.db.dateFormat(cal.time, “yyyy-MM-dd HH:mm:ss”)  
endDate = system.db.dateFormat(date2, “yyyy-MM-dd HH:mm:ss”)

#Select is a dataset with one column  
shifts = event.source.parent.Select  
shiftPyData = system.dataset.toPyDataSet(shifts)  
rows = []  
while cal.time.before(date2):  
num = 0  
for x in shiftPyData:  
value = shiftPyData[num][0]  
event.source.parent.getComponent(‘Label’).text = value  
query = “SELECT Starttime, Endtime from Shifts WHERE Shiftname = ‘%s’” % (value)  
results = system.db.runQuery(query)  
shiftStart = results[0][0]  
shiftEnd = results[0][1]  
rows.append([cal.getTime(), value, shiftStart, shiftEnd])  
num += 1  
cal.add(Calendar.DAY\_OF\_MONTH, 1)

#set up the final data variables  
table = event.source.parent.getComponent(“Table 2”)  
table.data = system.dataset.toDataSet([‘Date’, ‘Shift’, ‘StartTime’, ‘EndTime’], rows)[/code]