topic Re: Re: Data Mapping Issue in QlikView

Data Mapping Issue

Mon, 20 Oct 2014 17:38:34 GMT

Hi All,

I have Raw Data which contains Country Code like:

RAW DATA

IN-US

AU-IN

I have a master mapping table for country code like:

Country Code	Country Name
IN	INDIA
US	USA
CA	CANADA
AU	AUSTRALIA
MX	MEXICO

Now i need to map the Country Name field to the respective country codes in RAW DATA

EXAMPLE: For IN-US I need to get INDIA-USA

For AU-IN I need to get AUSTRALIA-INDIA

For CA I need to get CANADA

Thanks & Regards

Roopesh

Re: Data Mapping Issue

preminqlik — Mon, 20 Oct 2014 18:04:17 GMT

hi there,

find attachment

temp:

LOAD * INLINE [

Country Code, Country Name

IN, INDIA

US, USA

CA, CANADA

AU, AUSTRALIA

MX, MEXICO

];

join

Load

[Country Code] as CON_CODE,

[Country Name] as CON_NAME

Resident temp;

MAP_SUB:

mapping load [Country Code]&'-'&[CON_CODE] as COUNT_CODE_SUB,

[Country Name]&'-'&CON_NAME as COUNT_NAME_SUB

Resident temp;

MAP_NORM:

mapping Load distinct [Country Code],

[Country Name]

Resident temp;

drop Table temp;

DATA:

LOAD * INLINE [

RAW DATA

IN-US

AU-IN

];

LOAD *,

if(len(subfield([RAW DATA],'-',2))>0,ApplyMap('MAP_SUB',[RAW DATA]),ApplyMap('MAP_NORM',[RAW DATA])) as COUNTRY_NAME_FINAL

Resident DATA;

drop table DATA;

Re: Data Mapping Issue

Mon, 20 Oct 2014 18:17:51 GMT

Thanks Prem for the solution...

Re: Data Mapping Issue

MarcoWedel — Mon, 20 Oct 2014 21:17:34 GMT

Hi,

a short yet generic solution could be:

mapCountryCodes:
Mapping LOAD *
FROM [http://community.qlik.com/thread/138850] (html, codepage is 1252, embedded labels, table is @2);

tabCountries:
LOAD [RAW DATA],
    Concat(Country, '-', RowNo) as Countries
Group By [RAW DATA];
LOAD RecNo() as RecNo,
    RowNo() as RowNo,
    [RAW DATA],
    ApplyMap('mapCountryCodes',SubField([RAW DATA],'-')) as Country
FROM [http://community.qlik.com/thread/138850] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Re: Data Mapping Issue

MarcoWedel — Mon, 20 Oct 2014 21:41:09 GMT

To demonstrate that this solution is independent of the number of concatenated country codes per row:

mapCountryCodes:
Mapping LOAD Code, [Country name]
FROM [http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2] (html, codepage is 1252, embedded labels, table is @3);

mapCodeTemp:
Mapping LOAD RecNo() as ID, Code
FROM [http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2] (html, codepage is 1252, embedded labels, table is @3);

tabTemp:
LOAD Concat(Code,'-', IterNo) as [RAW DATA]
Group By RecNo;
LOAD RecNo() as RecNo,
     IterNo() as IterNo,
     ApplyMap('mapCodeTemp',Ceil(Rand()*249)) as Code
AutoGenerate 40
While IterNo() <= Ceil(Rand()*6);

tabCountries:
LOAD [RAW DATA],
     Concat(Country, '-', IterNo) as Countries
Group By [RAW DATA];
LOAD RecNo() as RecNo,
     IterNo() as IterNo,
     [RAW DATA],
     ApplyMap('mapCountryCodes',SubField([RAW DATA],'-',IterNo())) as Country
Resident tabTemp
While IterNo() <= SubStringCount([RAW DATA],'-')+1;

DROP Table tabTemp;

hope this helps also

regards

Marco

Re: Data Mapping Issue

d_pranskus — Mon, 20 Oct 2014 22:55:32 GMT

Another solution might be as follows:

raw:
LOAD * INLINE [
  data
  IN
  IN-US
  AU-IN
  MX
  CA
];

map:
MAPPING LOAD * INLINE [
  key, value
  IN, INDIA
  US, USA
  CA, CANADA
  AU, AUSTRALIA
  MX, MEXICO
];


tmp:
LOAD
  row,
  data,
  CONCAT(APPLYMAP('map', item), '-', id) AS text
GROUP BY row, data;
LOAD
  row,
  RECNO() AS id,
  data,
  SUBFIELD(data, '-') AS item;
LOAD
  RECNO() as row,
  data
RESIDENT raw;


DROP FIELD row;
DROP TABLE raw;

Darius