2 Replies Latest reply: Apr 28, 2014 12:41 PM by Traci Smith Lackey RSS

    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!

        • Re: Field Not Found Error
          m w

          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

              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!