topic ApplyMap Problem in QlikView

ApplyMap Problem

Tue, 26 Apr 2011 12:10:06 GMT

Hi guys, I have a problem using an applymap as described below.

Table1_Map:

LOAD Cust_Id,

Cust_Date

FROM .............;

Table2:

LOAD My_Date AS Cust_Date,

My_Year as Cust_Year,

My_Month as Cust_Month,

My_Day as Cust_Day,

My_WEEK as Cust_Week,

applymap('Table1_Map', Cust_Date)

FROM ...................;

When I run my script, I get an error message saying field not found - <Cust_Date>

I know it has something to do with the fact that I am renaming my fields in table 2. can someone suggest a way for me to fix this, without having to change my field renaming in table 2.

Thanks

Re: ApplyMap Problem

Miguel_Angel_Baeyens — Tue, 26 Apr 2011 12:21:33 GMT

Hello,

According to your code above, the mapping table needs the Cust_Id to return the Cust_Date, but my guess is that you want the opposite: given the Date, get the Id. If my assumption is correct, then the code is slightly different:

Table1_Map:

MAPPING LOAD Cust_Date,

Cust_Id

FROM .............;

Table2:

LOAD My_Date AS Cust_Date,

My_Year as Cust_Year,

My_Month as Cust_Month,

My_Day as Cust_Day,

My_WEEK as Cust_Week,

ApplyMap('Table1_Map', My_Date) AS Cust_Id

FROM ...................;

Hope that helps

ApplyMap Problem

Tue, 26 Apr 2011 12:29:57 GMT

Hi Miguel, I have tried out your suggestion, but it causes synthetic keys to be created and I end of getting 3 tables instead of 1.

Any more pointers or suggestions?

Thank you

ApplyMap Problem

stevedark — Tue, 26 Apr 2011 12:37:30 GMT

Hi there,

In your original code you were missing the keyword MAPPING in front of the load statement. If you look at Miguel's code you will see that this keyword is there. When you copied his code did you put this in there?

Without the MAPPING prefix the table is treated as a normal table. This means any ApplyMap statement using that table will fail and simply return null. It will also mean the table appears in the data model (which never happens with Mapping tables) and will link as appropriate.

It is very easy to miss the MAPPING prefix - especially as the wizard does not give an option to add it. It is always the first thing to check if you get null values back.

Hope that helps.

Regards,
Steve

ApplyMap Problem

Tue, 26 Apr 2011 13:00:42 GMT

Thanks guys, it is working now. I missed out the key words "MAPPING"

ApplyMap Problem

Tue, 26 Apr 2011 13:12:42 GMT

Hi,

Kindly refer the attached sample application

i hope it will help u

Regards,

Sampath Kumar . G

ApplyMap Problem

stevedark — Wed, 08 Jun 2011 05:59:31 GMT

Hi there,

You may find this link useful for more information on the ApplyMap Statement: http://bit.ly/kQcAZ5

It's a blog post detailing the syntax of the statement and it's various uses.

Regards,

Steve