I have a project in Ignition Vision, V7.9.16, where we are saving the energy (electricity, air) consumption, and good and bad pieces counters every hour to the tables in the PostgreSQL database.
I made a view in the PostgreSQL database, which combines two tables (one for energy and one for pieces count). This is what I get:
Pivoting flips rows to columns, but every DB I know will only source from one column to make the new columns (a grouping operation). I usually script this sort of thing. You might modify this function I use for simple tasks:
from com.inductiveautomation.ignition.common.util import DatasetBuilder
#----------
# Construct a dataset with columns from supplied datasets rows. The column
# names from the first dataset become the string values in the first column
# of the result. The first argument to the function is a sequence of names
# for the dataset arguments. The name string is used verbatim if the
# dataset has one row. Otherwise it is concatenated with the row number.
def flipData(names, *args):
ds1 = args[0]
firstcolumn = [x for x in ds1.columnNames]
types = [jString]
heads = ['_']
columns = [firstcolumn]
for dsn, ds in enumerate(args):
collist = range(ds.columnCount)
if ds.rowCount==0:
heads.append(names[dsn])
types.append(jObject)
columns.append([None for x in firstcolumn])
for rn in range(ds.rowCount):
if rn==0:
heads.append(names[dsn])
else:
heads.append("%s %d" % (names[dsn], 1+rn))
types.append(jObject)
row = [ds.getValueAt(rn, cn) for cn in collist]
for i, x in enumerate(row):
if isinstance(x, long):
row[i] = jLong(x)
columns.append(row)
rows = zip(*tuple(columns))
builder = DatasetBuilder.newBuilder().colNames(heads).colTypes(types)
for row in rows:
t = tuple(row)
builder.addRow(*t)
return builder.build()
Well, dataset pivoting itself already exists in my view() expression function. With a bit more functionality than you get with most DBs. But @zxcslo is after a more generic flipping of rows to columns, which my flipData() script does (not quite the way he wants, but close).
You won’t be able to use that function as-is. You’ll have to modify to your needs, as I mentioned. The architecture of the function is an example of flipping rows into columns.
For context, that function takes one or more similar datasets, that are expected to have one or just a few rows, and turns those rows into columns. The names argument provides the column names, one per dataset. For the datasets with more than one row, the corresponding result columns get a sequential suffix.
The data you feed to such a flipping function will have to be grouped to have just one row for the one column you want out of it.
As Phil implied, you would need to have your view be one row for each output column. The current view would need a bunch of ‘mini-pivots’, which are then rearranged (or smashed, if you prefer) into what you want.
If it’s not a straight flip, I’ll put the contents of the dataset into a dictionary, then construct the new dataset from the dictionary.
Untested, but it should illustrate.
pointList = ['AIR', 'ELECTRICITY', 'GOOD', 'BAD']
shiftDict = {1:{}, 2:{}, 3:{}}
dataDict = {}
# Arrange dataset into dictionary
for i in datasetIn.rowCount:
date = datasetIn.getValueAt(i,'DATE')
shift = datasetIn.getValueAt(i,'SHIFT')
# If date doesn't exits in the dictionary, add it.
if date not in dataDict.keys():
dataDict[date] = shiftDict.copy()
for point in pointList:
dataDict[date][shift][point] = datasetIn.getValueAt(i,point)
# Construct dataset
dateList = sorted(dataDict.keys())
dataOut = []
for shift in sorted(shiftDict.keys()):
for point in pointList:
newRow = []
for date in dateList:
newRow.append(dataDict[date][shift][point])
dataOut.append(newRow)
datasetOut = system.dataset.toDataSet(dateList, dataOut)
