How to format perspective table field?

I wanna format them to Sunday, Monday etc. Those numbers are from database and I do not wanna modify query.
image

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