Compare wildcard string to cell value and replace in canvas expression

[fuzzyWedgie]

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"
etc.
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:

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

| mapColumn "normalized"
fn={switch
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

[lukas (Lukas Olson)]

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

Select category.field, count(*) as events,
CASE
    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.

[fuzzyWedgie]

Great that works! thnx.

[fuzzyWedgie]

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