Hi
I want to create forecast data using loops?
Department,Region., Sales-amount, Date are the database fields
so i need to populate future dates of these four fields,able to populate
Department,Date ,flag
not able to populate Region,Salesamount
This is my code
-----------------------
CER:
LOAD DISTINCT
addmonths(Max(YearMonth),240) as MaxDate,
max(YearMonth) as MaDate,
min(YearMonth) as MinDate
FROM
$(FDirectory)\SALES.qvd(qvd);
LET CERMinDate = Num(Peek('MaDate', 0, 'CER'));
LET CERMaxDate = Num(Peek('MaxDate', 0, 'CER'));
SalesAmountDATA:
LOAD Department,
Region,
SalesAmount,
YearMonth
FROM
$(FDirectory)SALES.qvd(qvd);
let vCount = FieldValueCount('Department');
for i=1 to $(vCount)
let vItem = FieldValue('Department',$(i));
Concatenate (SalesAmountDATA)
LOAD '$(vItem)' as Department,
Date(AddMonths($(CERMinDate),IterNo()-1)) AS YearMonth
AUTOGENERATE 1 WHILE Date(AddMonths($(CERMinDate),IterNo()-1))<= ($(CERMaxDate));
NEXT
Flag:
NoConcatenate
LOAD *,
if(floor(YearMonth)<= Floor(Today()),'AsOn','Forecast') as Flag
Resident SalesAmountDATA;
DROP Table SalesAmountDATA;
DROP Table CER;
Left Join
LOAD YearMonth,
Department,
Region,
AVG(SalesAmount) as SalesAmount
Resident Flag
where Flag='AsOn'
Group by YearMonth,Department,Region;
New:
LOAD *,
if(Flag='AsOn',Department, if(Flag='Forecast', SalesAmount)) as FinalValue
Resident Flag;
DROP Table Flag;
please do the needful
Thanks
thiru