Hi,
i have a requirement to divide a factor called 'QUOTE' on per day basis!. i have written the below code but seems it doesnt work.
/**********************************************************************************************
**********************************************************************************************/
SET vMinYear=2014;
SET vMaxYear=2018;
LET vMinDate=num(MakeDate($(vMinYear), 1, 1));
LET vMaxDate=num(MakeDate($(vMaxYear), 1, 1));
//All Days of the given period
Quota_Days:
LOAD $(vMinDate) + rowno() -1 AS QuotaDay
AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;
Quota:
LOAD
%key1,
QUOTE AS Quota,
if(peek(%key1) = %key1, peek(EINSATZDATUM)-1, $(vMaxDate)) AS validTo,
num(date(EINSATZDATUM)) AS validFrom
RESIDENT MAIN_TABLE
ORDER BY %key1, EINSATZDATUM DESC;
//create Intervals to Dates
Quota_Intervals:
INTERVALMATCH (QuotaDay) LOAD DISTINCT validFrom, validTo
RESIDENT Quota;
DROP TABLE Quota_Days;
//join Dates into interval
LEFT JOIN (Quota)
LOAD
validFrom,
validTo,
QuotaDay,
Year(QuotaDay) AS QuotaYear
RESIDENT Quota_Intervals;
DROP TABLE Quota_Intervals;
//Make the yearly avg
LEFT JOIN (MAIN_TABLE)
//QUOTE:
LOAD
%key1,
QuotaDay,
QuotaYear ,
avg(Quota) AS Quota
RESIDENT Quota
GROUP BY
%key1,
QuotaDay;
QuotaYear;
DROP TABLE Quota;
Can someone help me correct this one? i get output just AVG of QUOTE and not divided by per day in that year of EINSATZDATUM (start date)
Thanks
Supriya
