Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using ApplyMap but getting Field not Found Error

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;

17 Replies
Not applicable
Author

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.

Not applicable
Author

Clever_Anjos
Employee
Employee

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

maxgro
MVP
MVP

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

Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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

Joseph_Musekura
Support
Support

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,