QlikView Documents

[mrooney]

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.

[Gabriel]

Hi,

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

[nikhilgarg]

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]

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

Richard

[mrooney]

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]

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]

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]

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]

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]

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]

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