Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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.
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;
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.
Can you run the load script in debug mode with animate selected to see which SQL query is giving the field not found error.
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;
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.
Try the sql statement with out double quotes.