Determining mobile/ cell number from two dimensions

  • 13 January 2023
  • 2 replies

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




2 replies



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}) 


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.