QlikView App Dev

[#1]

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;

[#2]

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.

[#3]

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

This link might help you.

[Clever_Anjos]

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

[maxgro]

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

[#6]

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;

[#7]

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

[#8]

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?

[#9]

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

[Joseph_Musekura]

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,