@PGriffith Below is my query datasource:
SELECT
startTime, endTime, onsiteHours, offsiteHours,
startTime1b, endTime1b, onsiteHours1b, offsiteHours1b,
startTime2, endTime2, onsiteHours2, offsiteHours2,
startTime2b, endTime2b, onsiteHours2b, offsiteHours2b,
startTime3, endTime3, onsiteHours3, offsiteHours3,
startTime3b, endTime3b, onsiteHours3b, offsiteHours3b,
startTime4, endTime4, onsiteHours4, offsiteHours4,
startTime4b, endTime4b, onsiteHours4b, offsiteHours4b,
startTime5, endTime5, onsiteHours5, offsiteHours5,
startTime5b, endTime5b, onsiteHours5b, offsiteHours5b
FROM ServiceReportDatabase
WHERE eventID = '{eventID}'
AND startTime IS NOT NULL
AND startTime1b IS NOT NULL
AND endTime IS NOT NULL
AND endTime1b IS NOT NULL
AND startTime2 IS NOT NULL
AND startTime2b IS NOT NULL
AND endTime2 IS NOT NULL
AND endTime2b IS NOT NULL
AND startTime3 IS NOT NULL
AND startTime3b IS NOT NULL
AND endTime3 IS NOT NULL
AND endTime3b IS NOT NULL
AND startTime4 IS NOT NULL
AND startTime4b IS NOT NULL
AND endTime4 IS NOT NULL
AND endTime4b IS NOT NULL
AND startTime5 IS NOT NULL
AND startTime5b IS NOT NULL
AND endTime5 IS NOT NULL
AND endTime5b IS NOT NULL
And then here is the script:
def updateData(data, sample):
queryData = system.dataset.toPyDataSet(data["startTimeQuery"])
columns = ["startTime", "endTime", "onsiteHours", "offsiteHours",
"startTime1b", "endTime1b", "onsiteHours1b", "offsiteHours1b",
"startTime2", "endTime2", "onsiteHours2", "offsiteHours2",
"startTime2b", "endTime2b", "onsiteHours2b", "offsiteHours2b",
"startTime3", "endTime3", "onsiteHours3", "offsiteHours3",
"startTime3b", "endTime3b", "onsiteHours3b", "offsiteHours3b",
"startTime4", "endTime4", "onsiteHours4", "offsiteHours4",
"startTime4b", "endTime4b", "onsiteHours4b", "offsiteHours4b",
"startTime5", "endTime5", "onsiteHours5", "offsiteHours5",
"startTime5b", "endTime5b", "onsiteHours5b", "offsiteHours5b"]
pivotedData = []
# https://stackoverflow.com/questions/434287/what-is-the-most-pythonic-way-to-iterate-over-a-list-in-chunks
# going to sets of four to "unpivot" your columns
def chunker(seq, size):
return (seq[pos:pos + size] for pos in xrange(0, len(seq), size))
for setOfValues in chunker(queryData[0], 4): # assuming only one row is returned for a given event ID
if all(setOfValues): # if all values are true == no values are None/null
pivotedData.append(list(setOfValues))
data["pivotedData"] = system.dataset.toDataSet(columns, pivotedData)
I believe there may be something wrong with the columns line. and maybe it is just because of my lack of understanding this script, but should I have all of the tags listed (“startTime”, “endTime”, “onsiteHours”, “offsiteHours”, “startTime1b”, “endTime1b”, “onsiteHours1b”, etc.) there or should I have listed the column headers of the actual table in the report (“Arrival / Remote Connection Time:”, “Departure / Remote Disconnection Time:”, “Onsite Hours:” and “Offsite Hours:”)?
Also, does it matter if the script or the query is listed first in the Data Sources?
