Convert UK Postcodes to Postcode Areas

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? 

   

0 4 899
4 REPLIES 4

Dawid
Participant V

Hi @pete-1651452632 

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

ca4e55d7-6cfb-48a8-bc9d-dd8abb73b00b.png

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)
)
)

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:

4f5a462e-2f82-4718-b12f-768812a5e9dd.png

Anything else I can try here? 

Cheers,

Pete

Hi @Dawid,

Anything else I can try here?

Dawid
Participant V

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 

Top Labels in this Space
Top Solution Authors