How to auto mapping constant value field just like sql "case when"?

You know it’s a typical scenario that uses SQL case when syntax to map integer constant value to a literal text. For example, an Order table contains a column called status which possible integer values are (1,2,3) and corresponding text are ('pending','success','failed').

However, I think it’s stupid if I should write such case when statement over and over. I hope Metabase have such a nice feature can support mapping constant field value automatically after a simple configuration in Data Modal.

I thought Data Modal -> Table -> FIELD SETTINGS -> Display values -> Custom mapping can achieve this, but not. It only applies to filter.

You could say that the database designer should never have a value encoded like that without having a lookup table to explain!

Easiest approach until something is added to metabase would be to create a view on the database along the lines of

SELECT 1, 'pending'
SELECT 2, 'success'
SELECT 3, 'failed'

Then use that as the foreign key target in the Data Model.

It would be nice to be able to define a derived table like that in Metabase though.

1 Like