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?
Thank you.
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.
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?
The syntax is simple:
select
case the_column_you_want_to_map
when 1 then 'string1'
when 2 then 'string2'
else 'default string'
end
from your_table
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:
translation_map = {
1: "string 1",
2: "string 2"
}
for row in value:
row['value'] = translation_map.get(row['value'], "default string")
return value
No luck with either of those methods. 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)?
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.
-
Change the Named query so it returns string instead of integer as suggested by @pascal.fragnoud
-
Apply a script transform to the name query, and using Python change the integer into a string.
Thanks.
1 Like
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.
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'.
@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!
That is simply improper SQL syntax for CASE. No, just missing a comma before the CASE.
Be sure to have commas between all of your columns. (In your screen shot, there is no comma between Route and your CASE statement)
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'
1 Like
Great catch, Thank you! That does it when using the in the query.
Do you happen to have any suggestions on the script transform?
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...
1 Like
