Data Mapping Issue

[#1]

Hi All,

I have Raw Data which contains Country Code like:

RAW DATA

IN

IN-US

AU-IN

MX

CA

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

[preminqlik]

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

    IN-US

    AU-IN

    MX

    CA

];

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;

[#3]

Thanks Prem for the solution...

[MarcoWedel]

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

[MarcoWedel]

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

[d_pranskus]

Hi

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