Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Mapping Multiple Fieds with only one Mapping Load

cancel
Showing results for 
Search instead for 
Did you mean: 
mrooney
Contributor III
Contributor III

Mapping Multiple Fieds with only one Mapping Load

Attachments

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)
Comments
Gabriel
Partner
Partner

Hi,

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

0 Likes
nikhilgarg
Specialist
Specialist

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

0 Likes
richard_pearce6
Partner
Partner

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

Richard

0 Likes
mrooney
Contributor III
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
Specialist
Specialist

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



0 Likes
neetha_p
Master
Master

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

0 Likes
mrooney
Contributor III
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.

0 Likes
mrooney
Contributor III
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.

0 Likes
anderseriksson
Partner
Partner

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.

0 Likes
sauliusr
Contributor II
Contributor II

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

0 Likes
Version history
Last update:
‎2014-12-21 10:48 AM
Updated by: