Compare wildcard string to cell value and replace in canvas expression

How can I replace all cell values in a table in canvas if it matches a wildcard string ?

I want to normalize some values in the table in a way that
if a table cell contains "test abc" I want the cell value to be replaced by "Test a1"
and if it contains value "test def", I want it to be replaced by " Test b2"
If none of them matches, Put " Not Mapped"

So I tried to do this but it doesnt work correctly, It now puts " Test a1" on all the cells:

count(*) as events
from index
where "category" like ' %test%'
group by category.field
order by events

| mapColumn "normalized"
case={case if={getCell "category.field" | string "test abc" in "category.field"} then="Test a1"}
case={case if={getCell "category.field" | string "test def" "category.field"} then="Test b2"}
default="Not Mapped"}
| table
font={font align="left" color="#000000" family="Arial, sans-serif" italic=false size=10 underline=false weight="normal"} paginate=false
| render

You could modify your SQL clause to be something like this:

Select category.field, count(*) as events,
    WHEN category.field LIKE '%test abc%' THEN 'Test a1'
    WHEN category.field LIKE '%test def%' THEN 'Test b2'
    ELSE 'Not Mapped'
END AS mapped_field
from index
where "category" like ' %test%'
group by category.field
order by events

Let me know if that helps.

Great that works! thnx.

It works to remap the category names in the column but then it doesnt aggregate on the new mapped_field.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.