Create Custom Table Rows

Archived Topics
1

Hi,
I am able to create a table with column header, but how do i create custom row for the same.

Example :

           Item A   Item B  Item C  (Column Header able to create)

ENo 1

ENo 2

ENo 3

How do i create (ENO 1 , ENo 2, ENo 3 - Rows ? )

Do i need to write scripts?

Thanks,
Vinay

2

The simplest way to add ENo 1, ENo 2, ENo 3 into the rows of the table is to use a ROW_NUMBER() type function. In SQL Server, it looks like:

ROW_NUMBER() OVER (ORDER BY column_name ASC) AS description

And you can use it in your query like:

SELECT 'ENo ' + ROW_NUMBER() OVER (ORDER BY itemname ASC) AS rowid, itemname, itemvalue FROM mytable
In MySQL, you have to put this in a stored procedure and use:

set @i = 0; select CONCAT('ENo ', @i:=@i+1) as rowid, itemname, itemvalue from mytable;
If you want to have random names in that column instead of numbers (ie: machine15, machine 22, machine 19 or east unit, west unit, south unit), you will need to do this with a python script that takes in the raw data set and creates a new one.

3

Hi,
The ENo 1, Eno 2, Eno 3 has to be custom rows created, like column header.

Thanks,
Vinay

4

OK, if you want to add custom rows its starts to become more difficult. Especially if you don’t know how many rows there will be in the dataset. Here is an example that will take a list of strings and put them in the first column of the dataset. It expects

  1. that there is a Dynamic Property called ‘rawData’ that is bound to a SQL query
  2. that there is a property named ‘data’ that is not bound to anything
  3. that it is being run from a propertyChange Event

if event.propertyName == "rawData": #get the raw dataset that contains 2 columns, ItemB and ItemC rawData = system.dataset.toPyDataSet(event.source.rawData) #create a list of all the string values you are going to put into the first column firstColumn = ["ENo1", "ENo2", "ENo3"] #create the final dataset headers and an empty dataset to construct it newHeader = ["ItemA", "ItemB", "ItemC"] newData = [] #create a counter for the row number i = 0 #iterate through the rows for row in rawData: #append a new row that includes the first column newData.append([firstColumn[i], row[0], row[1]]) #incriment the counter for the next row if it will be a valid index if i+1 < len(firstColumn): i += 1 #set the new data into the final dataset event.source.data = system.dataset.toDataSet(newHeader, newData)If the number of rows in rawData is more than the number of items in firstColumn, then the script will repeat the last item in firstColumn (instead of giving you an error).