Hi
I want to create three new columns from an existing dimension(called category) which has upto 3 keywords in it, with / in between them. EG
/Hobbies & Leisure/Special Occasions/Holidays & Seasonal Events
How can I do this in LookML? I tried the below:
dimension: cats {
description: "Top level category"
type: string
sql: substring(${category},1,position(${category},"/",)-1;;
}
And there were no LookMl errors but when I go in the explore I get:
SELECT substring(categories.category,1,position(categories.category,"/",)-1 AS categories_catsFROM `pulse_tables.categories` AS categoriesGROUP BY 1ORDER BY 1LIMIT 500
GROUP BY 1ORDER BY 1LIMIT 500
Solved! Go to Solution.
Okay, in this case I would do this:
SPLIT(${category}, "/")[SAFE_OFFSET(1)]
This would give you the second value, because the first will be empty since your string starts with your delimiter.
It looks to me like you’re missing a parenthesis:
sql: substring(${category},1,position(${category},"/",)-1);;
Thanks Dawid,
Good spot. Unfortunately when I added that in I still got the same error on my explore. Here is the code:
dimension: cats {
description: "Top level category"
type: string
sql: substring(${category},1,position(${category},"/",)-1);;
}
What dialect do you use? You also have a comma after the last argument of the position
position(${category}, “/”)
vs
position(${category}, “/”, ) ← this one has a comma making the parenthesis unexpected
Ah yes, still doesn’t like it though!
I’m using Google BigQuery Standard SQ:
SELECT substring(categories.category,1,position(categories.category,"/")-1) AS categories_a_1FROM `pulse_tables.categories` AS categoriesGROUP BY 1ORDER BY 1LIMIT 500
Here is the LookML again:
dimension: A {
description: "Top level category"
type: string
sql: substring(${category},1,position(${category},"/")-1);;
}
I changed the dimension name to check something but it’s supposed to be the same as the abover ‘cats’ dimension
BigQuery doesn’t have a function POSITION. You can perhaps use STRPOS but depending on what your data looks like, you might be able to use SPLIT, or even REGEX functions
Thanks Dawid. I’ll look into alternatives. The data is like below:
So I just want to extract words between the / delimeter
I tried using STRPOS instead of position. It compiled but gave no results
Okay, in this case I would do this:
SPLIT(${category}, "/")[SAFE_OFFSET(1)]
This would give you the second value, because the first will be empty since your string starts with your delimiter.
That’s great, thanks so much 🙂