
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Tags:
- qlikview_scripting
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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];


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
