topic Mapping Load - Apply Map Question in QlikView

Mapping Load - Apply Map Question

Mon, 28 Nov 2011 17:24:04 GMT

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;

Mapping Load - Apply Map Question

swuehl — Mon, 28 Nov 2011 17:38:46 GMT

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

Re: Mapping Load - Apply Map Question

Miguel_Angel_Baeyens — Mon, 28 Nov 2011 17:41:30 GMT

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

Re: Mapping Load - Apply Map Question

Wed, 20 Jun 2012 14:55:23 GMT

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

Re: Mapping Load - Apply Map Question

Jason_Michaelides — Wed, 20 Jun 2012 15:00:13 GMT

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

Re: Mapping Load - Apply Map Question

Thu, 28 Jun 2012 09:16:29 GMT

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