# Perspective Table - Column Transform **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective, database\ **Created:** [July 11, 2023, 2:35pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193 "2023-07-11T14:35:19Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 11, 2023, 2:35pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/1 "2023-07-11T14:35:19Z")
Hello All, I have a few tables bound to a query that is returning an integer in one of the columns. I would like to set up something like a map transform so that a string can be shown instead of the number. I have tried several different bindings to and on different components of the table and column, with different transforms applied. No success yet making it work within the table component. Primarily, there doesn't seem to be a good place to bind or reference a property with only the data for that column. Any recommendations for how to achieve this transform for the entire column? ![Capture](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/6/96676285458babd6437de61bac65d2c16034ff56.jpeg) Thank you. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [July 11, 2023, 2:43pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/2 "2023-07-11T14:43:57Z")
You could do that in the query, using `case`. Or add a script transform to the binding on `data` to process your data and replace the value. If you need more details, let me know which way you prefer. ---
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 11, 2023, 2:53pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/3 "2023-07-11T14:53:30Z")
I'm really interested in both. The case statement in the query may be useful because the same Named Query is called in a few other places, I will just have to ensure no negative effects. The script transform on the data binding would be useful for applying only on a as needed bases. Do you expect there is any way to apply it outside of the main binding? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [July 11, 2023, 3:08pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/4 "2023-07-11T15:08:46Z")
> [@Alex\_Guyer](#): > > The case statement in the query may be useful because the same Named Query is called in a few other places, I will just have to ensure no negative effects. The syntax is simple: ```sql select case the_column_you_want_to_map when 1 then 'string1' when 2 then 'string2' else 'default string' end from your_table ``` > [@Alex\_Guyer](#): > > The script transform on the data binding would be useful for applying only on a as needed bases. Do you expect there is any way to apply it outside of the main binding? You could instead create a custom property and use your initial binding there, then bind `data` to the custom property and transform it, so the initial non-mapped data is still available on the custom property. The transform is simpler if you change the return format of the query to json: ```python translation_map = { 1: "string 1", 2: "string 2" } for row in value: row['value'] = translation_map.get(row['value'], "default string") return value ``` ---
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 11, 2023, 6:49pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/5 "2023-07-11T18:49:38Z")
No luck with either of those methods. Just getting errors so far. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [July 11, 2023, 8:33pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/7 "2023-07-11T20:33:09Z")
> [@Alex\_Guyer](#): > > Just getting errors so far. Well what are the errors? Column[2] in your screenshot shows `1 st`, `2 nd` which are strings, not integers - unless you have some funky formatting on that column. Where are they coming from (and why is there a space in the string when they should be `1st, 2nd`)? ---
**Author:** ![Usman\_Ashraf](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/usman_ashraf/32/43168_2.png) [@Usman\_Ashraf](https://forum.inductiveautomation.com/u/Usman_Ashraf)\ **Post date:** [July 11, 2023, 8:44pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/8 "2023-07-11T20:44:38Z")
Hey @Alex_Guyer , Seeing the image, it seems like that you are creating a binding at a wrong place. The columns key in props is for configuring how the columns will display and the map transform is just going to change the value of the "numberFormat" key. So as suggested, two approaches. 1. Change the Named query so it returns string instead of integer as suggested by @pascal.fragnoud 2. Apply a script transform to the name query, and using Python change the integer into a string. Thanks. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [July 11, 2023, 8:51pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/9 "2023-07-11T20:51:48Z")
There's not much we can do without seeing the error and the code. Also, @Usman_Ashraf is right: That's not what `numberFormat` is for. @Transistor is also right: That column doesn't look like a number. I guess you'll need to tell us more about your setup. ---
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 12, 2023, 10:54am UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/10 "2023-07-12T10:54:24Z")
The 'numberFormat' property that I was working with had taken the 'ordinal' setting at that point I was working on it. Normally I have it to 'none'. ---
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 12, 2023, 11:03am UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/11 "2023-07-12T11:03:51Z")
@pascal.fragnoud I have some more details here. I must not have something quite right. So far no variation I have tried of the query or transform have worked. Thank you for the help! ![Capture1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/3/83e29f4a091a105a686489156abebc20ef99dfe3.jpeg) ![SQLerror](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/c/6c4c753a52e62ede5aa101050813d70f7e1cfd08.jpeg) ![SQL](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d843b0543d01f317143e15c627d300b2246e8f8.jpeg) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 12, 2023, 11:46am UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/12 "2023-07-12T11:46:57Z")
~~That is simply improper SQL syntax for CASE.~~ No, just missing a comma before the CASE. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [July 12, 2023, 11:46am UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/13 "2023-07-12T11:46:59Z")
Be sure to have commas between all of your columns. (In your screen shot, there is no comma between Route and your CASE statement) ```sql SELECT AddTime, Location, CASE Route WHEN 1 THEN 'string1' WHEN 2 THEN 'string2' ELSE 'default string' END AS Route FROM CNNWS WHERE AddClear = 'True' AND EOD = 'False' ``` ---
**Author:** ![Alex\_Guyer](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@Alex\_Guyer](https://forum.inductiveautomation.com/u/Alex_Guyer)\ **Post date:** [July 12, 2023, 12:43pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/14 "2023-07-12T12:43:46Z")
Great catch, Thank you! That does it when using the in the query. Do you happen to have any suggestions on the script transform? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [July 12, 2023, 12:59pm UTC](https://forum.inductiveautomation.com/t/perspective-table-column-transform/76193/15 "2023-07-12T12:59:33Z")
You need the modify the keys to match your actual data. In this case, `row['value']` should be `row['Route']`. Take a look at online python tutorials, at least the basics - dicts, lists, loops...