quick applymap example

[#1]

Would someone upload a small example of how to use applymap?  I'd really appreciate it!

[rustyfishbones]

Hi Chris,

I made a video on youtube on how to do a simple applymap

Qlikview ApplyMap by RFB 90

HTH

Al

[rustyfishbones]

BTW

It works just like VLOOKUP in Excel but the lookup table must only contain 2 columns

see this also

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

[Anonymous]

Hi,

Here is some code in the load script using apply map

Step 1: Create Mapping Table. This table is loading in the Alpha2CountryCode and DimCountryID from a QVD that stores country information.

ISOCode:

Mapping LOAD DimCountryID,

     Alpha2CountryCode

FROM

[$(vL.QVDPath)vDim_Country.qvd]

(qvd);

Step 2: Apply this map to transaction table. This will return the Alpha2CountryCode when i provide a valid ID. Otherwise Unknown.

Transactions:

LOAD

    DimDateID, 

     DimCustomerID,

     DimProductID,

    RevenueAmount,

    ApplyMap ('ISOCode', BookingLocationDimCountryID,'Country_Unknown')

FROM

[$(vL.QVDPath)Transactions.qvd]

(qvd);

[MK_QSL]

Please check enclosed example.

Also note that

1) Mapping Table should be loaded before the table in which ApplyMap need to be used

2) Mapping Table should be having exactly two columns.

[#6]

Ok, I think I see it now.

But why isn't 'BookingLocationDimCountryID' actually 'DimCountryID?'

[rustyfishbones · Accepted solution]

Hi Chris,

I cant upload an example, but here is some screen prints

I created a qvd of a mapping table containing 2 columns

I need to add Warehouse Description to my Chambers Table that contains a common field of Warehouse

Then in my chamber table I use the ApplyMap function to lookup against my mapping table above, so I can add the Warehouse Description to Chamber Table

Basically it has allowed me add the Warehouse Description to my Chambers table, like you would use the VLOOKUP Function in Excel

I hope that helps

[Anonymous]

In my transactions table I had a number of CountryIDs. This example uses the booking location. Essentially I am just passing the value of this ID into the mapping load. E.g. if the mapping load has the following and BookingLocationDimCountryID is 3, the apply map function will return SE.

ID, Alpha2

1, AU

2, NZ

3, SE

[stevedark]

Hi Chris,

You may find my blog post on ApplyMap useful, it details the basic and more advanced syntax of the command:

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

To answer your question about the field names; unlike fields that associate in the data model (where name matching is critical) there is no need to match the fieldnames with Mapping tables.  It only matters which field you load as column one (this is the field you are matching) and column two (the value you are applying).

Once you get your head around how to use ApplyMap you will find it an elegant way of solving many data load challenges.

- Steve

[#10]

Thanks all...

why  are we using mapping keyword??

is it mandatory that mapping table only have 2 colums??