Error in ApplyMap

[#1]

Attahed is the Sample application. Applymap is not working as required.

as in my application, my cleared no should be 2345 / 3456 and Cleared Amount should be 20 and 13 respectively.. which is not happening.. I need only the values below

2345	20
3456	13

the remaining values should not reflect.

[#2]

In order to use the "ApplyMap" function, this function can be defined (is this case the table is not created, can't be dropped):

Monthly:

Mapping LOAD No as M_No,

ID,

     Amt as M_Amt

FROM

Monthly.xlsx

(ooxml, embedded labels, table is Sheet1);

Master:

LOAD No as O_No,

ID,

     Amt as O_Amt

FROM

Master.xlsx

(ooxml, embedded labels, table is Sheet1);

Collected_Final:

Load *,

if (isnull (ApplyMap ('Monthly', O_No)), O_No) as Cleared_No ,

if (isnull (ApplyMap ('Monthly', O_No)), O_Amt) as Cleared_Amt

Resident Master;

Drop Table Master;

[#3]

Sorry.. But i dint get you.. Are you saying I cant drop this table and hence not possible?

If there is another way to do it, plz let me know..

[Sokkorn]

Hi,

Let try:

MonthlyClearNo:
Mapping
LOAD No as M_No, 
ID
FROM
Monthly.xlsx
(ooxml, embedded labels, table is Sheet1);
MonthlyAmt:
Mapping
LOAD No as M_No, 
     Amt as M_Amt
FROM
Monthly.xlsx
(ooxml, embedded labels, table is Sheet1);
Master:
LOAD No as O_No, 
ID,
     Amt as O_Amt
FROM
Master.xlsx
(ooxml, embedded labels, table is Sheet1);
Collected_Final:
Load *,
if (isnull (ApplyMap ('MonthlyClearNo', O_No)), O_No) as Cleared_No ,
if (isnull (ApplyMap ('MonthlyAmt', O_No)), O_Amt) as Cleared_Amt 
Resident Master;
Drop Table Master;

Do let me know.

Regards,

Sokkorn

[perumal_41]

Hi Shilpa,

if possible upload sample data in excel sheet for reference .

Regards

Perumal A

[#6]

Please find the sample data.. there are 2 values missing in monthly data and a few are added also..

[perumal_41]

Hi,I have attached simple application .this useful for u .

Regards

Perumal A

[#8]

Hi Shipa,

as far as I can see, your script should work with the use of the keyword "Mapping" like Enrique told in the first answer.

Monthly:

Mapping        // <------- use the keyword "mapping" to identify the following table as a mapping table !!

LOAD

    No as M_No,    

    ID,

    Amt as M_Amt

FROM

Monthly.xlsx

(ooxml, embedded labels, table is Sheet1);

Note:

The function Applymap() doesn't raise unfortunally any error if it can't find a fitting mapping table (in your case "Monthy")

HtH

Roland