topic Applymap using wildcard for UK postcodes in QlikView

Applymap using wildcard for UK postcodes

jlampard40 — Sat, 16 Nov 2024 17:46:57 GMT

Hi - I have a dataset which contains UK postcodes in following format:

BH21 3PL - Out of Area
BH30 2PH - Out of Area
BH1 1FG - Out of Area
SO16 6TF - In Area
SO21 4RT - In Area
SO1 4RE - In Area
SP10 3ES - Out of Area
SP4 2WQ - Out of Area
SP7 5PK - Out of Area
PO4 5TF - Portsmouth
PO21 3WD - Portsmouth
PO22 4TR - Portsmouth

I only really need the first section of the postcode which is either 2 letters and 1 number, or 2 letters and 2 numbers. Once I have this postcode in this format I can then apply some 'applymap' and allocate a map as above. There are obviously many more postcodes and areas mappings but I wanted to just give you a few examples to see what I'm referring to.

So, I want to somehow convert the long postcode format, as above to just two letters and one or two numbers, thus:

BH21 - Out of Area
BH30 - Out of Area
BH1 - Out of Area
SO16 - In Area
SO21 - In Area
SO1 - In Area
SP10 - Out of Area
SP4 - Out of Area
SP7 - Out of Area
PO4 - Portsmouth
PO21 - Portsmouth

Appreciate your help guys. Would like to somehow display this as a list box to select:

Out of Area
In Area
Portsmouth

Rgds

Re: Applymap using wildcard for UK postcodes

MayilVahanan — Wed, 25 Nov 2020 08:23:59 GMT

Hi @jlampard40

Try like below

Load *, SubField(PostalCodeWithArea,' ',1) as PostalCode,
Trim(SubField(PostalCodeWithArea, '-',-1))as Area,
SubField(PostalCodeWithArea,' ',1)&' - '&Trim(SubField(PostalCodeWithArea, '-',-1)) as ShortArea inline
[
PostalCodeWithArea
BH21 3PL - Out of Area
BH30 2PH - Out of Area
BH1 1FG - Out of Area
SO16 6TF - In Area
SO21 4RT - In Area
SO1 4RE - In Area
SP10 3ES - Out of Area
SP4 2WQ - Out of Area
SP7 5PK - Out of Area
PO4 5TF - Portsmouth
PO21 3WD - Portsmouth
PO22 4TR - Portsmouth
];

You can replace the inline with ur data source.

Re: Applymap using wildcard for UK postcodes

jlampard40 — Wed, 25 Nov 2020 09:54:49 GMT

Hi MayilVahanan - the only problem I have is a huge dataset with over 1.7 million different postcodes in format AA99 AAA or AA9 AAA (where A can be any letter combo specific to an area and 9 represents the number element of the postcode from 1 to 99).

I can't change the original data as it's stuck in the format as above. Ideally what I'd like is to be able to, somehow, read the postcode data and then clip it to JUST show the first part of it i.e. the AA99 or AA9 part of it. The other part I don't really need as it's just the general area I need to be able to define as 'In area' or 'Out of Area' for my data visualisation.

So how can I load all this data into Qlikview, tell Qlikview to trim / clip the first part and then allow me to use in a list box as 'In Area' or 'Out of Area'. This is a tricky one as there are so many lines of data, I need Qlikview to do trim / clip it automatically during load, then give me a dimension / field to use in a list box for 'In Area' or 'Out of Area'. Many thanks!

Re: Applymap using wildcard for UK postcodes

jlampard40 — Wed, 25 Nov 2020 10:35:36 GMT

Yes - we have Excel 2010 though (!) and the data is run on a daily basis as it's always a 'snapshot' of that day, so cannot trawl through the thousands of datasets and format the postcode through Excel. I really need to find a way through Qlikview to give me the first part of the postcode, during load, then allow me to define 'in' or 'out' of area depending on this first part of the postcode, in a list box. Hopefully someone on here can help me as I've reached a complete dead end here guys. Many thanks!

Re: Applymap using wildcard for UK postcodes

MayilVahanan — Wed, 25 Nov 2020 10:49:51 GMT

Hi @jlampard40

As mentioned earlier, you can replace with inline table to ur data source. You can use the logic.

Load *, SubField(PostalCodeWithArea,' ',1) as PostalCode,
Trim(SubField(PostalCodeWithArea, '-',-1))as Area,
SubField(PostalCodeWithArea,' ',1)&' - '&Trim(SubField(PostalCodeWithArea, '-',-1)) as ShortArea

Re: Applymap using wildcard for UK postcodes

jlampard40 — Wed, 25 Nov 2020 11:53:56 GMT

MAyilVahanan - that's great. Qlikview now presents the postcode as first part, but how can I then now apply mapping to this to say, if postcodes are, say, SO1 to SO16 it is 'In Area' and if it's say PO1 to PO20 it's defined as Portsmouth and all other postcodes are 'Out of Area'?

Thanks for help thus far MayilVahanan. Rgds

Re: Applymap using wildcard for UK postcodes

jlampard40 — Wed, 25 Nov 2020 15:23:33 GMT

What if I try a different technique and create a map in the usual way, the use this statement:

applymap('mytable', left(FIELD,4) )

My problem, however, is that some of the postcodes only have 3 digits in the first part, so will potentially be missed out? Is there any way of asking Qlikview to left trim EITHER 3 or 4 digits, depending on whether the postcode is in format AA9 AAA or AA99 AAA?