User defined functions in lookml

  • 19 August 2019
  • 4 replies

Userlevel 1

Hi Friends,

Is it possible to call user defined functions in looker?

I have a scenario i am getting string like ‘_20130808Fol180_246980-9_VK’ in one dimension. I want to split this string with **’’** and check if the first occurance with length 3 is string then show that value only. So in this case it should show ‘Fol’ only.

The position is not fixed so wanted to write a function which will split the string and loop to check the first occurrence.

The second option is to try implement user defined function in datawarehouse but wanted to check if we have possibility to write and call a function in looker also.


4 replies

Userlevel 3

How you want to split the string is not clear to me from your post, but it’s possible you can use regular expressions to get what you want. For example:

dimension: first_latin_alphabetic_substring {
sql: regexp_substr(${string}, '[a-zA-Z]+') ;; # Snowflake. YMMV in other dialects.
dimension: first_latin_alphabetic_substring_has_length_three {
type: yesno
sql: length(${first_latin_alphabetic_substring})=3 ;; # Snowflake. YMMV in other dialects.
Userlevel 1

Hi @menashe, Thanks for the idea.

I have to tweak little bit to get the first occurrence with length 3 so added {3} and it worked.

Final working condition :

regexp_substr( '_AB20130808Fol180_246980-9_VK' ,'[a-zA-Z]+{3}',1,1)

Userlevel 1

I was just curious if its possible. Since now you understood the requirement.

Now if we don’t want to use “regexp_substr” sql function. And if we want to write our own function in looker, javascript function or something and then call that function while populating the data.

Userlevel 2

@Krishna_Shukla it may not be feasible for your situation/requirements, but I believe you could also modify your view to use a derived table and in that sql, use your user-defined functions as you please.