I have a Dashboard which uses a table that with fields which have application names separated by commas.
Hostname | Titles | In-Service
server1 | Excel, Word, Workday, AutoCAD | Production
server2 | PowerPoint, Word, Workday, Insight | Production
server3 | Access, PowerPoint, Insight | Production
In my dashboard, I would like to create a custom calculation, dimension, or measure which would list & count the distinct applications
App | Count
Excel | 34
Word | 54
Workday | 12
AutoCAD | 4
PowerPoint | 14
Insight |5
Access | 9
Anybody know how to do this? (I am new to Looker and trying to learn)
How to count distinct apps in a comma separated lists stored in rows
Updating my example:
Hostname | Titles | In-Service
server1 | Excel, Word, Workday, AutoCAD | Production
server2 | PowerPoint, Excel, Word, Workday, Insight | Production
server3 | Access, PowerPoint, Word, Excel, Insight | Production
In my dashboard, I would like to create a custom calculation, dimension, or measure which would count the distinct applications. Results would look like this:
App | Title Count
Excel | 3
Word | 3
Workday | 2
AutoCAD | 1
PowerPoint | 2
Insight | 2
Access | 1
Userlevel 1
Hi Tyler,
For this you would need to leverage a database function. In Snowflake you could use something like split. Each dialect has its own logic/syntax but here’s a quick example:
with list_of_apps as (
select hostname, c.value::varchar as single_app
from server_list,
LATERAL FLATTEN(input=>split(replace(app_list,' '), ',')) C
)
select *
from list_of_appsIf you go the table calculation route you will have to use substring and position function but this wouldnt be the most scalable way. Here is some documentation on the 2 functions: https://help.looker.com/hc/en-us/articles/4419763659411-How-can-I-split-a-string-at-a-certain-character-with-a-Table-Calculation-or-Custom-Field-split-part-a-field-by-delimiter-Community-
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.