How to count distinct apps in a comma separated lists stored in rows

[Tyler_Lowe]

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)

[Tyler_Lowe]

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

[jcdufault]

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_apps

If 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-charac...