Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
laddu_927
Specialist
Specialist

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.

View solution in original post

3 Replies
laddu_927
Specialist
Specialist

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.

View solution in original post

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

Not applicable
Author

Thanks a lot. It is working.