topic Re: Field Not Found Error in QlikView

Field Not Found Error

Mon, 28 Apr 2014 16:30:28 GMT

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!

Re: Field Not Found Error

m_woolf — Mon, 28 Apr 2014 16:36:14 GMT

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];

Re: Field Not Found Error

Mon, 28 Apr 2014 16:41:48 GMT

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!