Initializing a dataset

Ignition
1

Hi,

I need to pivot a table and to do so, create a dataset with system.dataset.toDataSet(header, row).
The problem is that all the columns are in string type instead of int or double, and then the report cannot make any calculation on it.

I cannot initialize statically the header and the first row as the length is variable ; also, when I try to initialize the first row in the same loop as for the header, I get “IndexError: Row 0 doesn’t have the same number of columns as header list”…

Any idea ?

[code]DS = event.source.parent.getComponent(‘Table 1’).data
pODS = system.dataset.toPyDataSet(DS) #old pydataset
header = []
row = []
nbCol = DS.getColumnCount()
nbRow = DS.getRowCount()

#header
header.append(“Unité”)
for i in range (nbRow):
header.append(app.char.sans_accents(system.db.dateFormat(pODS[i][“t_stamp”],“MMM”)).upper())

#creation du new dataset
NDS = system.dataset.toDataSet(header, row)

#rows
for j in range(1,nbCol-1):
row.append(DS.getColumnName(j))
for i in range (nbRow):
row.append(int(pODS[i][j])*2)
NDS = system.dataset.addRow(NDS, row)
row = []

event.source.parent.getComponent(‘Table 2’).data = NDS[/code]

2

I would create the header row, then the data row, then create the data set.

[code]DS = event.source.parent.getComponent(‘Table 1’).data
pODS = system.dataset.toPyDataSet(DS) #old pydataset
header = []
rows = []
nbCol = DS.getColumnCount()
nbRow = DS.getRowCount()

#header
header.append(“Unité”)
for i in range (nbRow):
header.append(app.char.sans_accents(system.db.dateFormat(pODS[i][“t_stamp”],“MMM”)).upper())

#rows
for j in range(1,nbCol-1):
row = []
row.append(DS.getColumnName(j))
for i in range (nbRow):
row.append(int(pODS[i][j])*2)
rows.append(row)

#creation du new dataset
NDS = system.dataset.toDataSet(header, rows)

event.source.parent.getComponent(‘Table 2’).data = NDS[/code]

There is some magic that happens behind the scenes when the first data row is added to a dataset that determines what datatype each column should be. By creating a row with data in it first, it has a chance to guess correctly.

3

:smiley: :smiley:
Work nice.

4

Your welcome :thumb_right:

I’ve run into a smiler problem in the past where the different data rows rows held different data. In was fine in testing. The first row in production had a number in that column instead of a string so it complained on the second row.

Experience: what you get just after you need it :slight_smile: