Best way to concatenate in Looker?

worthywow
Participant II

Hi ! I’m a Looker newbie and have an initial query that would really appreciate any advice on.

I am working with recruitment tables where a job can have multiple locations but the end users want a report with a single row per job. Is it possible (and if so what’s the best way) to concatenate the location “rows” to a single field/row ?

Many thanks in advance for any help and advice.

0 8 7,787
8 REPLIES 8

Hi @worthywow , welcome to Looker!

When I read this type of question, I immediately translate “single row per X” to “X is the (only) dimension in this query”

The question then becomes, how can the rest of the fields be interpreted as measures? Most often, this is just a list measure, though there are other options.

As to how to define a list measure, you can either do that in LookML, or from your explore with custom measures defined at query time. (PS, if your instance is not yet opted-in to use custom fields, I 1000% recommend it)

worthywow
Participant II

Hi Fabio,

The List measure worked perfectly 😀

Really appreciate your support.

Kind regards,

Richard

Ashita
Participant I

The issue with list is that when one tries to download the report then all the characters are separated in different cells and defeats the whole purpose of concatenation in single cell. Please correct this feature. Or else suggest different solution apart from list.

blue1
Participant III

Hi Ashita,

you could create a measure that gets a distinct list of locations for every job and concatenate those using FOR XML Path (tested with Microsoft SQL Server 2016). You can use SQL syntax or Liquid to reference your variables. I have adapted the code to make sense for your use case.

measure: location_concat {
type: string
sql: (
SELECT DISTINCT J.JobCode,
SUBSTRING((
SELECT ‘,’+L.LocationName AS [text()]
FROM dbo.Location L
WHERE L.JobID = ${job.job_id}
ORDER BY L.LocationName
FOR XML PATH (’’)
), 2, 1000) [Locations]
FROM dbo.Job J) ;;
}

For larger datasets it can be a bit slow, so consider doing this for all jobs in a persistent derived table (PDT) in advance.

I hope this helps!

worthywow
Participant II

Hi Ashita,

I used the list approach and it seemed to download ok for me - I downloaded as excel and ticked the formatted option. The list all appeared in one cell. One thought is did you download as CSV in which case the separating commas will cause issues?

Kind regards,

Richard

worthywow
Participant II

Hi Jasper, Thanks for the alternative approach. Cheers, Richard

mogburn
Participant I

A related question.

One of our users is trying to do a list of a concatenated field. I’ve tried various options like Custom Measure, list and creating a Table Calculation. I tried the table calc with both:

list(${clients.full_name})

and 

list(concat(${clients.first_name}," ",${clients.last_name}))

All attempts return no results. 

Any suggestions how to make this work?

mogburn
Participant I

We learned the LookML wasn’t dealing well with Public Access features we have. Good to go now.

Top Labels in this Space
Top Solution Authors