topic Simple query on Mapping function in QlikView

Simple query on Mapping function

Wed, 05 May 2010 13:19:54 GMT

Hi,

Pardon my ignorence of qlikview but I have a small query which I guess cna be solved using mapping funciton. The only problem is that I don't know how to ...... [:$]

I have a list of countries whihc are to be mapped to a unique region

Country	Mapped Country
China	China/HK
Hong Kong	China/HK
Britain	UK
United Kingdom	UK
Ireland	UK
Isle of Man	UK

Now how do I need to edit the script so that whenever China is mentioned China/HK appears and if a country which is not mentioned in the table is there than the name as is appears.

Thanks

Simple query on Mapping function

Wed, 05 May 2010 13:25:38 GMT

hi umang,

u can use the following code

map1:

mapping LOAD * INLINE [

Country	Mapped Country
China	China/HK
Hong Kong	China/HK
Britain	UK
United Kingdom	UK
Ireland	UK
Isle of Man	UK

];

map Country using map1;

LOAD Country,

......

FROM

Country1.csv;

thanks

Re. :Simple query on Mapping function

martin59 — Wed, 05 May 2010 13:26:23 GMT

Could you send your application or an exemple please ?

Martin

Simple query on Mapping function

Wed, 05 May 2010 13:46:33 GMT

One more problem lets assume that Country is already loaded in the script previously under the table 'Details', in such case what do we need to do?

QV SCRIPT:

Details:
LOAD
Country,
Sales
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet1);

map1:
mapping LOAD * INLINE [
Country Mapped Country
China China/HK
Hong Kong China/HK
Britain UK
United Kingdom UK
Ireland UK
Isle of Man UK ];

// Need syntax as to what is to be written now .........
map Country using map1;
LOAD Country,

......
FROM
Country1.csv;

Simple query on Mapping function

Wed, 05 May 2010 14:03:12 GMT

hi umang,

probably u can then use something like this

Details:
LOAD
Country,
Sales
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet1);

map1:
mapping LOAD * INLINE [
Country Mapped Country
China China/HK
Hong Kong China/HK
Britain UK
United Kingdom UK
Ireland UK
Isle of Man UK ];

newtable:

load Sales,applymap('map1',Country) as resident Details;

drop table Details;

Simple query on Mapping function

Wed, 05 May 2010 14:16:05 GMT

tauqueer,

In the way you have suggested do I need to write all the fields of 'Details' again in the 'newtable' ???

Also I have updated a sample QV if you could edit the script in that would be a great help

Simple query on Mapping function

Wed, 05 May 2010 14:37:22 GMT

hi umang,

pls check the attached application.

probably this is what u want

thanks

Simple query on Mapping function

Wed, 05 May 2010 14:46:05 GMT

thanks with your help I was able to solve it

the script would look like this

map1:
mapping LOAD * INLINE [
Country, MappedCountry
China, China/HK
Hong Kong, China/HK
Britain, UK
United Kingdom, UK
Ireland, UK
Isle of Man, UK ];

map Country using map1;

Details:
LOAD
Country,
Sales,
Cost
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet1);