ApplyMap Problem

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

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

[stevedark]

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

[#5]

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

[#6]

Hi,

Kindly refer the attached sample application

i hope it will help u

Regards,

Sampath Kumar . G

[stevedark]

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