topic Re: Using ApplyMap but getting Field not Found Error in QlikView

Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 16:09:37 GMT

I am using applymap to add UserID, Region and UserName. Usually I would join but I read the blogs and found out ApplyMap would work on this and would save processing time. I keep getting the Field Not Found error, however, even though the table name is listed and spelled correctly and when I use the debugger, the mapping load is done correctly. I wonder if it is my SQL syntax because that is where I get the error but I reviewed the previously asked questions and followed the examples in the blogs and I do not understand why this error persists. Any help in explaining why I got the error would be beneficial to my growing knowledge.

//***Poster ID table****//
PosterID:
Mapping LOAD
AUDNAME,
AUDUID;
SQL SELECT "AUDNAME",
"AUDUID"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Region Name table***//
Region:
Mapping LOAD
MGR,
REG;
SQL SELECT "MGR",
"REG"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Poster Name table***//
PosterName:
Mapping LOAD
AUDUID,
AUDNAME;
SQL SELECT "AUDUID",
"AUDNAME"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Deposit Summary & Checks Table with Region and User Info***//
Deposits:
LOAD *,
    if(Age <=7,'0 - 7',
    if(Age >7 and Age <=14,'8 - 14',
    if(Age >14 and Age <=21,'15 - 21',
    if(Age >21 and Age <=28,'22 - 28',
    if(Age >28 and Age <=35,'29 - 35',
    if(Age >35 and Age <=42,'36 - 42',
    if(Age >42 and Age <=49,'43 - 49',
    if(Age >49 and Age <=56,'50 - 56',
    if(Age >=57,'57+'))))))))) as AgeCat;

LOAD "CHECK_SOURCE" as CheckSrc,
    "DEPOSIT_DATE" as DepositDate,
    "NUMBER_CHECKS" as NumberChecks,
    "VALIDATOR_ID" as ValidatorID,
    Interval(Today()-DEPOSIT_DATE,'DD') as Age,
    "CHECK_AMT" as CheckAmt,
    "CHECK_DATE" as CheckDate,
    "CHECK_ID" as CheckID,
    "APPLIED_AMT" as AppliedAmt,
    (CHECK_AMT - APPLIED_AMT)>0 as Unapplied_Amt,
ApplyMap('PosterID',AUDUID)as UserID,
ApplyMap('Region',REG) as Region,
ApplyMap('PosterName',AUDNAME) as UserName;
SQL SELECT *
FROM HUM.TFNDPBH,
HUM.TFNDPCK;

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 16:47:24 GMT

I believe you must use Key Fields in your mappings. In mapping, the first field is considered the key field so you have key fields of AUDNAME, MGR, and AUDUID. However, you don't load any of these fields in your final table. This may be your problem.

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 16:47:54 GMT

http://www.learnqlickview.com/applymap-function-in-qlikview/

This link might help you.

Re: Using ApplyMap but getting Field not Found Error

Clever_Anjos — Tue, 03 Jun 2014 16:53:03 GMT

Do you have AUDUID,REG,AUDNAME fields into your SQL query?

Re: Using ApplyMap but getting Field not Found Error

maxgro — Tue, 03 Jun 2014 17:06:24 GMT

could you post the log of your reload?

or try replacing all

mapping load field1, field2;

with

mapping load *;

and let we know

or put some

exit script;

to see where is the error

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 17:13:07 GMT

Hi,

Following Select is dangerous.

Selecting from two tables but no join between them so it will yield cartesan product. if this is what you want just ignore my comment.

Second, * wil lget all fields from both tables if a column appears in both it will generate error too.

I first start with the following statement and write clearly with join condition and

SQL SELECT *
FROM HUM.TFNDPBH,
HUM.TFNDPCK;

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 18:28:59 GMT

I tried that and got this error: "AUDUID" is not valid in the context where it is used" when I added AUDUID, REG, and AUDNAME. (I switched REG and MGR because I need the region not the manager).

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 18:33:34 GMT

I need the all of the records from both HUM.TFNDPBH and HUM.TFNDPCK otherwise I would have used a join. I was advised use ApplyMap because the Map Auditor table is just to give regions, names and UserIDs for the checks in the two above mentioned tables. Would it be better to separate those two and then do applymap() to the two separate tables?

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 18:34:55 GMT

The error appears in the SQL Select statement after ApplyMap. I have replaced * with just the fields I want.

Re: Using ApplyMap but getting Field not Found Error

Joseph_Musekura — Tue, 03 Jun 2014 18:57:32 GMT

Hi,

Please check the comparison and desired value.

When using “applyMapp”, we need to understand that the mapping table consists of two columns, the first containing comparison values and the second containing the desired mapping values (see qlikview manual).

For example in your case

PosterID:
Mapping LOAD
AUDNAME,
AUDUID;
SQL SELECT "AUDNAME", "AUDUID"

FROM FR_OPR_PRO.TBLMAPAUDITOR;

Here the Comparison value is = AUDNAME and the desired value is = AUDUID.

So in your script you will have

ApplyMap('PosterID',AUDNAME)as UserID,
ApplyMap('Region', MGR) as Region,
ApplyMap('PosterName', AUDUID) as UserName;

Regards,

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 19:01:35 GMT

In the original script, I have 3 mapping tables so I can get the AUDNAME and AUDUID because I need. I'm wondering if this is a problem. For the first one, maybe I should remove AUDNAME and use another comparison field. I'll try that.

Re: Using ApplyMap but getting Field not Found Error

Colin-Albert — Tue, 03 Jun 2014 19:11:03 GMT

As stated by Ersen B the error is in your SQL statement and is not an error in the QlikView mapping function.

SELECT *
FROM HUM.TFNDPBH,
HUM.TFNDPCK;

You need to specify the join between the two tables in your select statement, or load each SQL table separately into QlikView, and sort out the field names to get the correct association between the tables.

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 19:28:05 GMT

Here's the code. Took out one table for right now. Got the Field not Found error. Added the fields in. Got this error: "AUDUID" is not valid in the context where it is used" when I added AUDUID, REG, and AUDNAME. If I take it out, I get the Field Not Found error, which pretty much sends me back to my original question.

PosterID:
Mapping LOAD
SUPID,
AUDUID;
SQL SELECT "SUPID",
"AUDUID"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Region Name table***//
Region:
Mapping LOAD
MGR,
REG;
SQL SELECT "MGR",
"REG"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Poster Name table***//
PosterName:
Mapping LOAD
SUP,
AUDNAME;
SQL SELECT "SUP",
"AUDNAME"
FROM FR_OPR_PRO.TBLMAPAUDITOR;

//***Deposit Summary with Region and User Info***//
Deposits:
LOAD *,
if(Age <=7,'0 - 7',
    if(Age >7 and Age <=14,'8 - 14',
    if(Age >14 and Age <=21,'15 - 21',
    if(Age >21 and Age <=28,'22 - 28',
    if(Age >28 and Age <=35,'29 - 35',
   if(Age >35 and Age <=42,'36 - 42',
    if(Age >42 and Age <=49,'43 - 49',
    if(Age >49 and Age <=56,'50 - 56',
    if(Age >=57,'57+'))))))))) as AgeCat;
LOAD "CHECK_SOURCE" as CheckSrc,
    "DEPOSIT_DATE" as DepositDate,
    "NUMBER_CHECKS" as NumberChecks,
    "VALIDATOR_ID" as ValidatorID,
    Interval(Today()-DEPOSIT_DATE,'DD') as Age,
    ApplyMap('PosterID',AUDUID)as UserID,
ApplyMap('Region',REG) as Region,
ApplyMap('PosterName',AUDNAME) as UserName;
SQL SELECT "CHECK_SOURCE",
"DEPOSIT_DATE",
"NUMBER_CHECKS",
"VALIDATOR_ID",
"AUDUID",
"REG",
"AUDNAME"
FROM HUM.TFNDPBH;

Re: Using ApplyMap but getting Field not Found Error

Tue, 03 Jun 2014 19:34:15 GMT

Hi,

what is the source DB or?

Can you execute your SELECT statement outside?

Please do it so and see the results.

You can first select your fields and LOAD * from that select see what are you getting in Qlikview if you can not run in another tool.

Re: Using ApplyMap but getting Field not Found Error

Colin-Albert — Tue, 03 Jun 2014 22:03:36 GMT

Can you run the load script in debug mode with animate selected to see which SQL query is giving the field not found error.

Re: Using ApplyMap but getting Field not Found Error

Mon, 09 Jun 2014 13:06:59 GMT

This is the SQL query giving me the issue.

SQL SELECT "CHECK_SOURCE",
"DEPOSIT_DATE",
"NUMBER_CHECKS",
"VALIDATOR_ID",
"AUDUID",
"REG",
"AUDNAME"
FROM HUM.TFNDPBH;

Re: Using ApplyMap but getting Field not Found Error

Mon, 09 Jun 2014 13:11:34 GMT

The SQL select statement is very simple and when I do it outside of Qlikview, I get results with no errors. It's not until I add the fields from the mapping tables in Qlikview do I get issues.

Re: Using ApplyMap but getting Field not Found Error

Mon, 09 Jun 2014 14:02:31 GMT

Try the sql statement with out double quotes.