i have two numeric dimensions - home phone and work phone. I want to create a custom dimension “Cell phone” that extracts from either field the mobile or cell phone number.
so for example
Home Phone may equal 94991234
Work Phone may equal 0400000000
so my custom dimension “Cell Phone” should equal 0400000000
Coming for oracle Bi this was a simple task using a cast function but how can i achieve this in Looker?
I dont want to use a table calculation and don't have access to lookML
thanks
Mark,
What is the logic you are using to determine whether the home phone or the work phone gets taken to be the cell phone? (i.e. in your example why does the work phone get selected?)
In general, you should be able to use looker functions in a custom dimension to get this:
if([some logic that results in yes if we should take from home], ${home_phone}, ${work_phone})
Hi
This is my current oracle formula
CASE WHEN CAST("Client"."Home phone" AS CHAR(2)) = '04' THEN "Client"."Home phone" WHEN CAST("Client"."Work phone" AS CHAR(2))= '04' THEN "Client"."Work phone" WHEN CAST("Client"."Home phone" AS CHAR(1)) in ('1','2','3','4','5','6','7','8','9') then CONCAT ('03', CAST("Client"."Home phone" AS CHAR)) Else NULL End
Essentially convert to a string then determine whether to use home phone or work phone.
If this is the best approach then It appears in looker i cannot use a to_string type function and this needs to be set up in lookML.
The issue i have with all this is i dont have access to lookml as looker is provided by the application vendor. Oracle Bi has so much more end user functionality.
thanks