Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

User defined functions in lookml

  • 19 August 2019
  • 4 replies
  • 1279 views
Krishna_Shukla
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.


Thanks.

4 replies

menashe
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.

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

Krishna_Shukla
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.

cole.elliott
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.

Reply


Terms of Service