Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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.
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.
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
Thanks a lot. It is working.