Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Field Not Found Error

I'm fairly new to the nuances of Qlikview and I'm baffled as to why something so simple can be so hard. I am calculating two fields in the load script, Age and AgeCat. AgeCat is dependent on calculating the age. If I put the calculation in the SQL SELECT statement, it doesn't work and gives me Oracle errors. If I put it in the load statement, I get the field not found error. Here is the script.

LOAD "CHECK_SOURCE" as CheckSrc,
   
"DEPOSIT_DATE" as DepositDate,
   
"NUMBER_CHECKS" as NumberChecks,
   
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,
Interval(Today(),DEPOSIT_DATE) as Age;

SQL SELECT "CHECK_SOURCE",
"DEPOSIT_DATE",
  "NUMBER_CHECKS"
  FROM [TABLE_NAME];

(Table name omitted for security purposes.)

I can put the bold code in the bottom of the load statement or at the top and I get field not found error. I put it in the SQL SELECT statement get a syntax error. I've put the statement in the SQL statement and get errors stating the function is not valid. Any help would be appreciated!

1 Solution

Accepted Solutions
mwoolf
Honored Contributor II

Re: Field Not Found Error

In your LOAD statement, the Age field is not known until after the load.

Try a preceding load:

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,
Interval(Today(),DEPOSIT_DATE) as Age;

SQL SELECT "CHECK_SOURCE",
"DEPOSIT_DATE",
  "NUMBER_CHECKS"
  FROM [TABLE_NAME];

2 Replies
mwoolf
Honored Contributor II

Re: Field Not Found Error

In your LOAD statement, the Age field is not known until after the load.

Try a preceding load:

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,
Interval(Today(),DEPOSIT_DATE) as Age;

SQL SELECT "CHECK_SOURCE",
"DEPOSIT_DATE",
  "NUMBER_CHECKS"
  FROM [TABLE_NAME];

Not applicable

Re: Field Not Found Error

Michael,

I can't thank you enough. I had tried a preceding load statement but I was putting the age calculation instead of the AgeCat calculation. I'm learning about how QV reads scripts and this is VERY helpful! I've been trying to figure this out for 2 days!