Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Mapping Load - Apply Map Question

My understanding is that when you use the command applymap, the script, looks in the table you mapped, finds the field you specify, and then returns the value of that field.  I am reviewing someone elses work and I see what it is doing but it does make sense.  In this statement,

applymap('mapCustClass', [Customer Customer Class Code], 'Unassigned') as [Customer Customer Class],

The field [Customer Customer Class Code] does not exist in the mapCustClass table, only A1 and A2 exist.  However, the value being returned in the applymap is the value of A2.  So, how can the applymap function find the field [Customer Customer Class Code] (which originally was the [Customer classific._KUKLA] field) in the customer table and know that field is the A2 field in the mapCustClass table that was mapped?  I am assuming this is what it is doing by the results being generated.  Can someone help me understand this a bit better?  Thanks,

mapCustClass:
MAPPING
LOAD DISTINCT
[Customer classific._KUKLA] as a1,
[Customer classific._KUKLA] & '-' & Description_VTEXT as a2
FROM TKUKT.qvd (qvd)

Customer:
LOAD
Name_NAME1 as [Customer Name],

Customer_KUNNR,
[Customer classific._KUKLA] as [Customer Customer Class Code]

FROM ZBI_KNA1.qvd (qvd)

Left join (Customer)
LOAD DISTINCT
Customer_KUNNR,
applymap('mapCustClass', [Customer Customer Class Code], 'Unassigned') as [Customer Customer Class],

RESIDENT Customer;

5 Replies
swuehl
MVP
MVP

The field names in the mapping table are not relevant, it just uses the first field to match the given value and returns the corresponding value in the second field of the mapping table.

Please have a look into Steve's blog:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Regards,

Stefan

Miguel_Angel_Baeyens

Hi,

Your thoughts are right: mapping tables don't care about field names. If you prefer, you can call the first field the input field and the second, the output field. Whenever the second parameter of the ApplyMap() function finds a value in the mapping table specified as the first parameter, it will always return the value in the second field. However, this is not the only function for mapping tables. You can change field names or table names as well using a mapping table. Again, in this case the mapping table field names don't care either.

Think of this mapping table

TitleMap:

MAPPING LOAD * INLINE [

Short, Long

Mr., Mister

Dr., Doctor

Ms., Miss

];

Customers:

LOAD CustomerID,

     CustomerName,

     CustomerTitle

     ApplyMap('TitleMap', CustomerTitle) AS CustomerLongTitle

FROM CustomerTable;

The ApplyMap might read: "check the table TitleMap for the input value of CustomerTitle and return the output value and store it into a field named  CustomerLongTitle".

That's the way mapping tables work.

Hope that makes sense.

Miguel

Not applicable
Author

Is it possible to use the mapping load to include a 'catch all' expression?

i.e

CASE [field]

WHEN aTHEN 1

WHEN b THEN 2

.

.

ELSE 99

Jason_Michaelides
Luminary Alumni
Luminary Alumni

That is the 3rd parameter.  So in the example above:

applymap('mapCustClass', [Customer Customer Class Code], 'Unassigned') as [Customer Customer Class],

...if the current value of [Customer Customer Class Code] exists in the map then the mapped value will be returned, otherwise (ELSE) 'Unassigned' will be returned.

Hope thsi helps,

Jason

Not applicable
Author

Thanks -- dont kno whow i missed that :S doh!