Convert UK Postcodes to Postcode Areas

Looker Q&A Looker
, ,
1

Hello!

I am wanting to visualise sales in the UK on a map.

Based on documentation, Looker allows for Postcode Areas (e.g., Norwich: “NW”) but not actual postcodes (e.g., NR11AA). Unfortunately, the Shopify data I am working with only has full postcode data.

So, I am wanting to pull only the postcode area. Tricky thing is, some postcode areas are two letters, others are three. Therefore, can’t use a simple substring($shopify_zip,1,2) table calc.

We’re using a shared data warehouse, so can’t use LookML. Will need to be a table calculation or custom filter.

Could someone please assist with this?

2

Hi @pete-1651452632

It’s possible. I tested it on few examples:

Ideally we would have some regex functions in table calculations, but we don’t have it. Your substring function is a good start but as you mentioned, sometimes there’s one letter or two, hence you have to make it dynamic.

Try the following:

concat(
  substring(${test.postcode}, 1, 1),
  if(to_number(substring(${test.postcode}, 2, 1)) < 10, 
    "", 
    substring(${test.postcode}, 2, 1)
  )
)
3

Hi Dawid,

Thanks for this! Works well to create a custom table calculation, but not as a custom dimension (which the map visualisation requires).

For some reason, Looker is not picking up zip when trying to create a custom dimension:

Anything else I can try here?

Cheers,

Pete

4

Hi @Dawid ,

Anything else I can try here?

5

Well, if the postcode isn’t there, I would focus on finding out the reason why is that. If it’s just another dimension, it should be avaiable for custom dimension.

Unfortunately, I can’t help you with that as I don’t have access to your LookML but