topic quick applymap example in QlikView

quick applymap example

Tue, 12 Nov 2013 21:53:03 GMT

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

Re: quick applymap example

rustyfishbones — Tue, 12 Nov 2013 21:57:39 GMT

Hi Chris,

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

Qlikview ApplyMap by RFB 90

HTH

Re: quick applymap example

rustyfishbones — Tue, 12 Nov 2013 21:59:39 GMT

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/

Re: quick applymap example

Anonymous — Tue, 12 Nov 2013 22:02:21 GMT

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);

Re: quick applymap example

MK_QSL — Tue, 12 Nov 2013 22:15:04 GMT

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.

Re: quick applymap example

Tue, 12 Nov 2013 22:23:50 GMT

Ok, I think I see it now.

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

Re: quick applymap example

rustyfishbones — Tue, 12 Nov 2013 22:33:33 GMT

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

Re: quick applymap example

Anonymous — Tue, 12 Nov 2013 22:45:12 GMT

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

Re: quick applymap example

stevedark — Tue, 12 Nov 2013 23:48:39 GMT

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

Re: quick applymap example

Tue, 11 Nov 2014 11:08:53 GMT

Thanks all...

why are we using mapping keyword??

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

Re: quick applymap example

Tue, 11 Nov 2014 11:32:47 GMT

Hi vishnupriya,

if u r not using mapping keyword then it won't consider as a mapping table .

so mapping is mandatory .

s its mandatory to u have 2 coloumns , if u have more than that take a resident of that table with 2 coloumns and apply applymap

Re: quick applymap example

Tue, 11 Nov 2014 11:41:48 GMT

Thanks for giving answer.

i am new to Qlik View.please give me reply for these questions.

what is the use of resident??

why we will get synthatic keys??

How to remove synthatic keys??

Re: quick applymap example

Tue, 11 Nov 2014 11:48:36 GMT

Resident load:

loading the data from previously loaded table

if example i have 100 fields in one table but for my work i want only 5 fields that time i simply go with like

products:

apple

banana

fruit

orange

sapota

jama

by using resident: u want only banana ,jama means

load

banana,

jama

resident products; - so simply u can load fields which u want , no need to load all

( 5 to 6 fields u can go for comment , if 100 fields are there and u want only 5 that case we use resident)

synthetic keys:

when we have more than one common field between 2 tables then we get synthetic key

ways to remove:

commenting field,

renaming field

qualifier and unqualifier ,

Ekkada nerchukuntunaru vishnu priya

if this answer helpful to u pls make it as a correct answer

Re: quick applymap example

Tue, 11 Nov 2014 12:34:34 GMT

Hi,

what is the use of resident:

If you want to apply any transformation just use resident load for groupby and order by and having by clauses like that we can use resident load instead of doing normal load.it will give gud performance.already existing table if we wwant to load once again that is called resident load.\

why we will get synthatic keys:

if we have morethan 1 common key in moretahn 1 table then the synthetic key table will form.it will give may or maynot perfect result better to avoid it.

to remove synthetic keys:

renaming field,

qualifier and unqualifier statement ,

Re: quick applymap example

Tue, 20 Jan 2015 21:35:21 GMT

Hello My friend, I want to learn how i can use the Mapping sentence and Applymap function. I have the following script, but it doesn't work. Coul you help me please? Thanks.

map1:

mapping LOAD * INLINE [

id, valor

a1, uno

a2, dos

a3, tres

a4, cuatro

];

LOAD

@1 as id,

@2 as Nombre,

@3 as Ciudad,

ApplyMap('map1',valor,'descnonoci') as valor

FROM

prueba.txt

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Re: quick applymap example

stevedark — Tue, 20 Jan 2015 22:12:58 GMT

Hi Leonardo,

The second parameter of the ApplyMap function is the field that you are mapping on to, I would expect this to be either @1, @2 or @3 - depending on which field contains the values a1,a2,a3 and a4.

The blog post I reference below has quite a lot of detail on the various options for the parameters.

Hope that helps.

Steve

Re: quick applymap example

Wed, 21 Jan 2015 13:32:12 GMT

Hello Steve, Thanks for your clarification. Now it works.

The correct expression is: ApplyMap('map1',@1,'descnonoci') as valor

Have a nice day.

Re: quick applymap example

Thu, 23 Jul 2015 07:31:41 GMT

Great Learning Post!

Re: quick applymap example

Anonymous — Mon, 18 Jul 2016 15:13:44 GMT

thanks Alan found that video very easy to understand and make sense of Applymap

Re: quick applymap example

Mon, 22 Aug 2016 13:10:09 GMT

Thanks for the exellent example.