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:
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 ];
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!
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!
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'?
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?