Qlik Community

QlikView Documents

Documents for QlikView related information.

Mapping Multiple Fieds with only one Mapping Load

mrooney
New Contributor III

Mapping Multiple Fieds with only one Mapping Load

Hi there,

I recently needed to map several fields from one table to another. May be I'm a little bit lazy, so I used a very simple but useful workaround to do this job with only one Mapping Load.

Hope it helps.

As always, comments are welcome.

Miguel.

Labels (2)
Attachments
Comments
Gabriel
Valued Contributor II

Hi,

Excellent job and another way of using APPLYMAP with mapping multiple field.

nikhilgarg
Contributor III

Hey,

Actually i am using PErsonal edition so cannot open your file. Can you please tell me what is the script because as far i know mapping is done if mapping table has only 2 fields. I really want to know how to map with multiple fields.

Please tell.

Thanks

richard_pearce6
Valued Contributor

Great Idea Miguel, I'll use this technique in the future

Richard

mrooney
New Contributor III

Hi Nikhil,

here you are the script.

Countries:

Load * INLINE [

CountryCode,CountryName,CountryCurrency

ESP, Spain,EUR

UK, United Kingdom, GBP

USA, United States, USD

JPN,Japan, JPY

AUS, Australia, AUD

]

;

MAP_COUNTRIES:

Mapping LOAD

  CountryCode,

  CountryName

  & '|' &

  CountryCurrency as CountryData

Resident Countries;

Customers:

Load * INLINE [

CustCode,Sales,CusCountry

1, 2000, ESP

2, 3000, AUS

3, 5000,JPN

4, 1000, ESP

5, 500, USA

6, 1500, UK

7, 6000, AUS

];

[Customer Master]:

LOAD *,

  Subfield(ApplyMap('MAP_COUNTRIES',CusCountry,Null()),'|',1) as CountryName2,

  Subfield(ApplyMap('MAP_COUNTRIES',CusCountry,Null()),'|',2) as CountryCurrency2

Resident Customers;

DROP Table Customers;

Regards, Miguel.

nikhilgarg
Contributor III

HEy,

I didn't get one thing. In your folowing code:

ApplyMap('MAP_COUNTRIES',CusCountry,Null())


After MAP_COUNTRIES , there should be such a column that if matches with other table column should create a new column CountryData else NULL.


I didn't get why you use CusCOuntry ??


Plz tell

Thanks



neetha_p
Honored Contributor

Hi Miguel,

Yes,Excellent idea.So we can map multiple fields by concatenating.

Here data between two concatenated fields is having one to one relationship.

can we use these solution for one to many,many to many relationship data fields also?

Thanks

Neetha

mrooney
New Contributor III

Hi Nikhil,

i'm not sure i understand your point. You are right, after MAP_COUNTRIES there should be a key field from the main table to match with the mapping table key field. In this example such field is CusCountry.

Regards.

M.

mrooney
New Contributor III

Hi Neetha,

If i right understand what you mean, the answer is yes for the one to many case. Respect to many to many relationships I guess it should work as long as you concatenate key fields in mapping and target tables.

Regards.

anderseriksson
Contributor III

Granted, it works but why bother?

You could just as easily do two mapping loads and then have simpler ApplyMaps.

It solves a problem that does not need solving.

sauliusr
New Contributor II

You loose important default behavior by this method- to return unchanged value if corresponding mapping key is not found in mapping table.

Version history
Revision #:
1 of 1
Last update:
‎12-21-2014 10:48 AM
Updated by: