Field not found error when i use calculated field (Age) in IF condition.

[#1]

I am new to Qlikview. Could you please help me?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD [Project Name],

     [Requestor Name],

     [Project Manager],

     [Estimate Architect],

     [AD Estimator],

     [Function Point Counter],

     [ITO Estimator],

     [Lead Estimator],

     AMSME,

     [ITO Architect],

     [ITO Project Manager],

     Region,

     [Line Of Business],

     [Project Start Date],

     [Project Completion Date],

     [Project Description],

     [Project SDM Size],

     [ITMS Application Number],

     [ITMS Project Number],

     [Estimate Type],

     [ITO Funded Only],

     [Rework F],

     [Estimate Group],

     [Pre Estiamte Meeting Date],

     Status,

     [Actual Completion Date],

     [Request Id],

     [Proposed Estimate Delivery Date],

     SDM,

     [Clarity Number],

     [Vendor Supplier Led],

  [Request Date],

  Month([Request Date])as MOR,

  Year([Request Date]) as YOR,

  month([Actual Completion Date]) as MOC,

  year([Actual Completion Date]) as YOC,

    floor(today()-[Request Date]) as age,

    if(age<30,'<30',if(age<60,'60',if(age<90,'90'))) as aging

    [New Model Program],

     [Funding Region]    

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous · Accepted solution]

If  age obtained here "floor(today()-[Request Date]) as age," is used as age in if(age<30,'<30',if(age<60,'60',if(age<90,'90'))) as aging, It will not work as the execiton of statement happens once. So you can either use    if(floor(today()-[Request Date])<30,'<30',if(floor(today()-[Request Date])<60,'60',if(floor(today()-[Request Date])<90,'90'))) as aging. Or use another load statement to calculate aging.

[Peter_Cammaert]

That's to be expected. Just like in SQL, you can't use columns that you create during the same LOAD/SELECT. The order in which you specificy columns isn't necessarily the same as the one of effective column creation (optimiser, you know)...

Use a PRECEDING LOAD to make use of Age in another calculation or IF construct, like in:

TableName:

LOAD *, IF (age<30,'<30', IF (age<60,'60',IF (age<90,'90'))) as aging;

LOAD [Project Name],

     :

     Floor(Today()-[Request Date]) as age,

FROM ...;

Best,

Peter

[#4]

Thanks a lot. It is working.