Need Looker Help creating Custom Dimension using multiple functions

[DMorin]

Hello! Hoping someone is able to help in what is seeming to become a rather complicated custom dimension.

usecase: There is a field in an existing database called "code". Possible outputs are similar to HS1, HS2. I'm trying to update this output based on another field in the same datatable (fee). If the fee is 0, then I want to create a new 'code' with a "P" inserted in the middle of the code, such that if fee = 0, code = HSP1, or HSP2

I'm getting stuck in how to insert a letter conditionally in the middle of a string.

Thank you!

[GavinW]

Here is an approach that might work - bear in mind that anything in the SQL: parameter will need to be customised to work with your database dialect, but in principle you could create a dimension like:

dimension: new_field {
type: string
sql:
CASE
WHEN ${fee_field} = 0 THEN
-- HERE IS YOUR LOGIC FOR INSERTING P 
>insert_p_logic<
ELSE
${code} -- Keep the original code if fee is not 0
END ;;
}

And in the place I've marked to insert the logic, you can use a substring function (tailored to your DB) to slot in a 'P' at the right place. It will look something like this:

CONCAT(SUBSTR(${code}, 1, 2), 'P', SUBSTR(${code}, 3))

So you would slot that logic into the dimension where it says

>insert_p_logic<

It might take a little bit of tweaking but I think it should work.