Question

Convert UK Postcodes to Postcode Areas

  • 11 September 2022
  • 4 replies
  • 44 views

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? 

   

 

 


4 replies

Userlevel 7
Badge +1

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

 

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

Hi @Dawid,

Anything else I can try here?

Userlevel 7
Badge +1

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 

Reply