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