topic Re: Error in ApplyMap in QlikView

Error in ApplyMap

Mon, 23 Jul 2012 07:09:36 GMT

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.

Re: Error in ApplyMap

Mon, 23 Jul 2012 07:27:27 GMT

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;

Re: Error in ApplyMap

Mon, 23 Jul 2012 11:35:36 GMT

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

Re: Error in ApplyMap

Sokkorn — Mon, 23 Jul 2012 11:42:45 GMT

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

Re: Error in ApplyMap

perumal_41 — Mon, 23 Jul 2012 11:42:46 GMT

Hi Shilpa,

if possible upload sample data in excel sheet for reference .

Regards

Perumal A

Re: Error in ApplyMap

Mon, 23 Jul 2012 11:59:08 GMT

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

Re: Error in ApplyMap

perumal_41 — Mon, 23 Jul 2012 12:44:02 GMT

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

Regards

Perumal A

Re: Error in ApplyMap

Mon, 23 Jul 2012 12:51:29 GMT

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