Transform a column in a Perspective table

Ignition
1

Hi all!

In Perspective, how do I transform a value to a string in a table?
So the 0 will be “Bad” etc etc.

I basicly know how to use the transform binding but i don’t understand how to pass the correct value to the transform.

2

There are actually a couple ways to go about this. Assuming you’re using a query binding to populate your table, you could utilize a ‘case’ expression in your query to map the values of a column to strings. This saves you some scripting if you’re more comfortable with your database. Stackoverflow with example here.

If you want to go the script transform route, here’s a way to do so. I prefer the json return format for query bindings since it’s easy to dig into in Python.

image
image979×293 34.8 KB

Add a script transform. From there, accessing and altering the data is pretty simple; you can iterate through each row in your dataset and apply logic to each row as needed. You also can’t write back into value, so you’ll have to recreate your dataset (as far as I am aware).

# map out your numbers to your strings. dict is the pythonic way to do so
statusCodes = {0:"Bad", 1:"Good"} # etc.

# transformed dataset that will be returned at the end
newDataset = []

# iterate rows
for row in value: # value is the dataset passed into the transform from binding
    
    # create new row for newDataset
    newRow = {} 
   
    # iterate columns in each row
    for col in row:
        newColValue = row[col] # get each column's value
        if col == "Status": # or w/e the name of the column you want to transform is
            newColValue = statusCodes[newColValue] # change value into status code
        # if there are other columns you want to change values for,
        # you can add additional 'elif' statements after this 'if'
        
        # add data to our new row
        newRow[col] = newColValue
    
    # add the newly created row to the new dataset and start the loop again for next row
    newDataset.append(newRow)

return newDataset

Hope this helps!

3

Thanks! I changed the query, lots easier than the code.

1 Like