Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

quick applymap example

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

1 Solution

Accepted Solutions
rustyfishbones
Master II
Master II

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

2013-11-12_2216.png

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

2013-11-12_2216_001.png

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

View solution in original post

22 Replies
rustyfishbones
Master II
Master II

Hi Chris,

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

Qlikview ApplyMap by RFB 90

HTH

Al

rustyfishbones
Master II
Master II

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
Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

Ok, I think I see it now.

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

rustyfishbones
Master II
Master II

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

2013-11-12_2216.png

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

2013-11-12_2216_001.png

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Thanks all...

why  are we using mapping keyword??

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