# Table in Perspective: replace numeric values in a table with textual representation

**URL:** <https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [April 25, 2024, 2:31pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452 "2024-04-25T14:31:02Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [April 25, 2024, 2:31pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/1 "2024-04-25T14:31:02Z")

</div>

In Ignition Perspective, I want to replace numeric values (1,2,3) in a table with textual representations like "moderate", "torelable", "danger", etc.,

---

<div class="post-metadata">

**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:** [April 25, 2024, 2:37pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/2 "2024-04-25T14:37:17Z")

</div>

Options:

1. Do that when generating the data. e.g., In SQL.
2. Run a script transform on the Table component `data` to replace the numbers in the required column.

If you need more help then supply more information.

- What is the datasource?
- Is it JSON or a dataset?

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [April 25, 2024, 2:41pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/3 "2024-04-25T14:41:06Z")

</div>

Now is just a fixed dataset I copied on the table but in the future would be a Named Query linked to a Connection

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/3/93ecf92533369f372a83bdeac647a918c867d57d.png)

Ok, I try conditionally changing the table with an external view and something like:

```python

if({view.params.rowData.RIESGO} == "1", "Important",
   if({view.params.rowData.RIESGO} == "2", "tolerant",
     if({view.params.rowData.RIESGO} == "3", "fine", "")))

```

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [April 25, 2024, 3:35pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/4 "2024-04-25T15:35:01Z")

</div>

If it's going to be a named query, return the content as `json` instead of `auto`. Then, attach the following transform:

```python
def transform(self, value, quality, timestamp):
	# pay attention to the returned type of the values. If they're actually strings, you'll
	# need to cast them before running them through the dict, or change 1 to "1".
	translations = {
		1: "Important",
		2: "tolerant",
		3: "fine"
	}
	# the following variable should contain each of the columns which you need to translate
	columns_to_translate = [
		"population"
	]
	new_data = []
	for row in value:
		row_object = {}
		for column in row:
			if column in columns_to_translate:
				data = translations.get(row[column], "")
			else:
				data = row[column]
			row_object[column] = data
		new_data.append(row_object)
	return new_data

```

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [April 25, 2024, 3:42pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/5 "2024-04-25T15:42:25Z")

</div>

Will do tomorrow, good day.

---

<div class="post-metadata">

**Author:** ![ironyeti1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ironyeti1/32/36635_2.png) [@ironyeti1](https://forum.inductiveautomation.com/u/ironyeti1)\
**Post date:** [April 30, 2024, 3:00pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/6 "2024-04-30T15:00:24Z")

</div>

If you have a lot of data, running a script to update every row might be inefficient. A solution I implemented, and as implied by @Transistor, can be embedded in the query itself with appropriate formatting. Note my example is from Postgresql but the concept should be transferrable. If Named Queries had option to return json like the query bindings do this wouldn't be as complicated (\*cough feature request \*cough).

Essentially, update your main query to generate the styling values based on a criteria (level in this case), and then use an outer query to convert that into psuedo-json results.

```SQL
SELECT FORMAT(
  '{
    "level":{"value":%1$s, "style":{"backgroundColor": "%3$s","color": "%4$s"}}, 
    "text":{"value":"%2$s", "style":{"backgroundColor": "%3$s","color": "%4$s"}}, 
  }', level, REPLACE(text, '"', ''''), bgColor, fgColor) AS json_formatted

FROM (
  SELECT
    level, 
    text, 
    CASE 
      WHEN level < 200 THEN '#F1F1F1'
      WHEN level < 400 THEN '#F1F1F1'
      WHEN level < 600 THEN '#FBFFC7'
      WHEN level < 800 THEN '#FFD4D4'
      WHEN level < 1000 THEN '#FA7D7D'
      ELSE '#3B3B3B'
    END AS bgColor, 
    CASE
      WHEN level < 200 THEN '#828282'
      WHEN level < 400 THEN '#000000'
      WHEN level < 600 THEN '#000000'
      WHEN level < 800 THEN '#000000'
      WHEN level < 1000 THEN '#FFFFFF'
      ELSE '#FFFFFF'
    END AS fgColor
  FROM my_table
  WHERE ...

```

This returns rows of results that are formatted like json, but aren't combined into a single object yet. Pump those results through a quick list comprehension and viola, your data is ready to be bound to the table.

```Python
ds = system.db.runNamedQuery("yourNamedQuery", params)
data = [system.util.jsonDecode(row[0].replace("\n", "")) for row in ds] # the \n is byproduct of outer query so we fix it here.

```

EDIT: replaced `literal_eval()` with `system.util.jsonDecode()` per reply from @paul-griffith

This will yield a json formatted structure compatible with the table data object.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d638c08e09ced2d849630c02ca759a08cc9f209.png)

If your data has lot of rows, this solution should be more performant.  
If your data has lots of columns needing this, then a pure Python solution like @cmallonee's is probably better.

---

<div class="post-metadata">

**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:** [April 30, 2024, 3:06pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/7 "2024-04-30T15:06:08Z")

</div>

> [@ironyeti1](#):
>
> `literal_eval(`

Ewww! No, don't go there. `eval()` is almost never safe.

---

<div class="post-metadata">

**Author:** ![ironyeti1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ironyeti1/32/36635_2.png) [@ironyeti1](https://forum.inductiveautomation.com/u/ironyeti1)\
**Post date:** [April 30, 2024, 3:19pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/8 "2024-04-30T15:19:49Z")

</div>

@pturmel my understanding was `ast.literal_eval()` was much safer than just `eval()` as the former only accepted pre-defined python types, whereas the latter execuates before evalation. Is literal\_eval() that worrisome?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 30, 2024, 3:35pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/9 "2024-04-30T15:35:19Z")

</div>

If you have JSON, then use `system.util.jsonDecode`. `ast.literal_eval` is 'safer' in the sense that it's a gun with the bullets taken out, but safety aside, it's also _dramatically_ slower than parsing to JSON - standing up the AST parsing mechanism is expensive.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 30, 2024, 3:37pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/10 "2024-04-30T15:37:58Z")

</div>

> [@ironyeti1](#):
>
> A solution I implemented, and as implied by @Transistor, can be embedded in the query itself with appropriate formatting. Note my example is from Postgresql but the concept should be transferrable.

Not all JDBC drivers support SQL scripts, so MMV depending on the DB in use.

---

<div class="post-metadata">

**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:** [April 30, 2024, 3:40pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/11 "2024-04-30T15:40:19Z")

</div>

> [@lrose](#):
>
> SQL scripts

Ironyeti1's example in #6 is a single statement, not a script.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 30, 2024, 4:07pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/12 "2024-04-30T16:07:30Z")

</div>

Well,

That’s what I get for replying before I think.

😑

---

<div class="post-metadata">

**Author:** ![ironyeti1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ironyeti1/32/36635_2.png) [@ironyeti1](https://forum.inductiveautomation.com/u/ironyeti1)\
**Post date:** [April 30, 2024, 5:43pm UTC](https://forum.inductiveautomation.com/t/table-in-perspective-replace-numeric-values-in-a-table-with-textual-representation/89452/13 "2024-04-30T17:43:00Z")

</div>

Thank you @paul-griffith, I compared `system.util.jsonDecode()` against `ast.literal_eval()` and it was definitely faster.