Creating an 'other' for catch-all?

Wed, 05 Jan 2011 12:28:18 GMT

Hello!!

I'm loading a spreadsheet for postcode prefixes and their region (ie "MK" = "Midlands") and joining on the first part of the full postcode...

However there are some postcode prefixes in my data which do not have a match in the spreadsheet.

I'd like to set those records' prefixes to have a value of 'other' on load something like:


PostCode,
if (isnum(mid(PostCode,2,1)), left(PostCode,1),Left(PostCode,2)) as PostCode_Part1,
if(exists( [ PostCode_Part1 in the Excel file ] ),'PostCode_Part1','Other') as PostCode_Part1b,

This is the xls load statement:


LOAD Prefix as PostCode_Part1,
Region
FROM MyXLS.xls

This 'Other' value in PostCode_Part1b will then match with the 'Other' row in the spreadsheet which will return 'Other' as a region...

Does that make sense??? Thanks! Mike

Creating an 'other' for catch-all?

Wed, 05 Jan 2011 13:12:58 GMT

Hello again... After taking a step back from the problem I think I might have found my own solution...

ApplyMap??


Map_Region:
MAPPING LOAD
 Upper (Prefix) as PostCode_Prefix,
 Region
FROM

E:\QlikViewDocuments\Test\HNW_by_region\Data\Regions.xlsx
(ooxml, embedded labels, table is Sheet1);

... and then apply the map with another load statement above the orginal load:


LOAD
 *,
 ApplyMap('Map_Region', Upper(PostCode_Part1),'Unknown') as Region;

topic Creating an 'other' for catch-all? in QlikView

Creating an 'other' for catch-all?

Creating an 'other' for catch-all?