I wanna format them to Sunday, Monday etc. Those numbers are from database and I do not wanna modify query.
A script Transform on the dataset is probably going to be the best route. Loop through the rows and either add on an additional column with the value you want, or replace the value in the dataset.
1 Like
Thanks for your info, could you please share me a demo that reformat cell value of current field with a script Transform?
I would take a look at the link below. It doesn’t have an example to do exactly what you are seeking, but the ‘Dataset to Array with Renamed Columns’ section probably is the closest. It gives code to loop through the dataset and rebuild it as a dictionary.
https://docs.inductiveautomation.com/display/DOC80/Script+Transform
1 Like
In case you haven't been able to write it yet, here's how I would do it:
# convert the incoming value data
pyData = system.dataset.toPyDataSet(value)
# get the header names
header = pyData.getColumnNames()
# create a blank list so we can append later
newList = []
#dictionary that holds your days. The key will be the same as the value in your database.
dayDict = {1: "sunday", 2: "monday"}
# step through the rows
for row in pyData:
# create a new blank dictionary for each row of the data
newDict = {}
# use an index to step through each column of the data
for i in range(len(row)):
#if this is your column where you want to display the days
if header[i] == "dayCol":
#replace the value of row[i] with the corresponding value from your dictionary
row[i] = dayDict[row[i]]
# set name/value pairs
newDict[ header[i] ] = row[i]
# append the dictionary to list
newList.append(newDict)
# return the results
return newList
The only real modifications I made were:
#dictionary that holds your days. The key will be the same as the value in your database.
dayDict = {1: "sunday", 2: "monday"}
And
#if this is your column where you want to display the days
if header[i] == "dayCol":
#replace the value of row[i] with the corresponding value from your dictionary
row[i] = dayDict[row[i]]
Hi @RadicalRumin and @grietveld , I tried out your code with Transform feature, it seems we should return a BasicStreamingDataset data type, I am running on V8.0.5
My solution:
if(value is None):
return value
logger = system.util.getLogger("myLogger")
logger.info('DT value: '+str(value))
dayDict = {1: "Sunday", 2: "Monday", 3: "Tuesday", 4: "Wednesday ", 5: "Thursday", 6: "Friday", 7: "Saturday"}
# convert the incoming value data
# convert the incoming value data
pyData = system.dataset.toPyDataSet(value)
logger.info('DT pyData: '+str(pyData))
# get the header names
header = pyData.getColumnNames()
# create a blank list so we can append later
newList = []
newHeader =[]
for col in header:
newHeader.append(col)
logger.info('Header : '+str(header))
logger.info('New Header : '+str(newHeader))
# step through the rows
for row in pyData:
# create a new blank dictionary for each row of the data
newDict =[]
#logger.info('Row : '+str(row))
# use an index to step through each column of the data
for i in range(len(row)):
# set name/value pairs
#logger.info('Row i : '+str(row[i]))
curCell = row[i]
if header[i] == "Day":
#replace the value of row[i] with the corresponding value from your dictionary
curCell = dayDict[curCell]
newDict.append( curCell)
# append the dictionary to list
newList.append(newDict)
logger.info('DT newList: '+str(newList))
returnValue = system.dataset.toDataSet(newHeader, newList)
logger.info('DT returnValue: '+str(returnValue))
# return the results
return returnValue