topic clean data in QlikView

clean data

Thu, 12 May 2011 18:29:10 GMT

Hi! I loaded a field containing zip codes. To make it neat I would like them to be formated as 'xxx xx', and most of them are, but not all. My first thought was to do it while loading by manipulating the string BUT I also have zip codes for three other countrys (country_id stored in another field). Is it the best way to do a temp table and then map that to the main table? Twice actually since I have to do it for all other countries as well. In SQL i would just set a condition, WHERE country_id=1, but I can't do that in loading? Only when loading resident, right?

clean data

Thu, 12 May 2011 19:28:51 GMT

Omitted zip code from main, loaded zip, country_id and customer_id and then created MAPPING and ApplyMap. Switched the loading order so I create the mapping tables first and then I run the big one, with ApplyMap to get my clean swedish zip codes. Now I made another MAPPING for the other countries. I want them in the same field, naturally. So how do I merge the two MAPPINGS????

Re: clean data

rbecher — Thu, 12 May 2011 20:09:50 GMT

Hi Thomas,

I would suggest to make mappings for each country. This would assure a separated treatment and no overlapping.

In the script you could try to use the mapping related to the country or make concatenated loads for each country with a Where Clause on country_code:

zip_code_SE:

MAPPING LOAD..

zip_code_GB:

MAPPING LOAD..

data:

LOAD ApplyMap('zip_code_' & country_code, zip_code) as zip_code...

- Ralf

Re: clean data

Thu, 12 May 2011 20:50:59 GMT

Hi Ralf! But won't that force me to execute main script twice (or as many times as I have countrys)? Won't I get as many tables as I have countrys? Right now I like to do something like this:

ApplyMap('new_zip_sweden',SITE_ID,ApplyMap('new_zip_not_sweden',SITE_ID,'')) as SITE_ZIPCODE

NO errors running the script but only not_sweden is correct. Not a friend of this editor!!!

clean data

rbecher — Fri, 13 May 2011 05:15:17 GMT

No, you need not to run the script twice in my solution.

Btw. you have an error in your nested ApplyMap, this should work:

ApplyMap('new_zip_sweden', ApplyMap('new_zip_not_sweden', SITE_ID)) as SITE_ZIPCODE

clean data

Mon, 16 May 2011 09:01:53 GMT

Concatenated zip_fix for countries and then joined that.

clean data

rbecher — Mon, 16 May 2011 09:11:27 GMT

What this mean? Can you give us more explanations..

clean data

Mon, 16 May 2011 09:18:24 GMT

Concatenated into one table:

new_zip:
LOAD...
WHERE country=1
concatenate LOAD...
WHERE country<>1

And then I Joined new_zip back to main LOAD