Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Set MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
[Outlets]:
Load
OutletID,
City,
Province,
Region,
Distributor,
Opening_Date,
Start_Date,
Closing_Date,
MonthlyOutletRental,
"OutletSize(Sq meter)",
"# of shop assistants",
PayrollCost
FROM [lib://Tupperware Data QVD (hpserver_qsadmin)/AllOutlets.qvd]
(qvd);
NoConcatenate
[AllOutlets]:
Load
OutletID,
City,
Province,
Region,
Distributor,
Opening_Date,
Start_Date,
Closing_Date,
MonthlyOutletRental,
"OutletSize(Sq meter)",
"# of shop assistants",
PayrollCost
Resident Outlets
order by Opening_Date desc, Closing_Date desc;
Drop Table Outlets;
[Temp1]:
Load
if(max(Opening_Date)>max(Closing_Date),max(Opening_Date),max(Closing_Date)) as Opening_Date
Resident AllOutlets;
//Let vMaxDate = date(monthend(today()),'DD-MM-YYYY');
Let vMaxDate = peek('Opening_Date', 0, 'Temp1');
trace 'Max Date'&$(vMaxDate);
//for i = 0 to NoOfRows('AllOutlets')
for i = 0 to 10
Let vOutletID = peek('OutletID', $(i), 'AllOutlets');
Let vCity = peek('City', $(i), 'AllOutlets');
Let vProvince = peek('Province', $(i), 'AllOutlets');
Let vRegion = peek('Region', $(i), 'AllOutlets');
Let vDistributor = peek('Distributor', $(i), 'AllOutlets');
Let vOpening_Date = peek('Opening_Date', $(i), 'AllOutlets');
Let vClosing_Date = peek('Closing_Date', $(i), 'AllOutlets');
Let vOutletRental = peek('MonthlyOutletRental', $(i), 'AllOutlets');
Let vOutletSize = peek('"OutletSize(Sq meter)"', $(i), 'AllOutlets');
Let vNumofassist = peek('"# of shop assistants"', $(i), 'AllOutlets');
Let vPayrollCost = peek('PayrollCost', $(i), 'AllOutlets');
Let vCloseDate2 = if(len(trim('$(vClosing_Date)')) > 0,'$(vClosing_Date)','$(vMaxDate)');
Let vCloseDate3 = Date(DATE#(vCloseDate2,'DD-MM-YYYY'),'DD-MM-YYYY');
Let vOpenDate2 = Date(DATE#(vOpening_Date,'DD-MM-YYYY'),'DD-MM-YYYY');
Let vCounter = $(MonthDiff(vOpenDate2, vCloseDate3));
Let vMonth = Peek('Opening_Date', $(i), 'AllOutlets');
for j = 0 to ($(vCounter)-1)
[MonthTableTemp]:
Load
OutletID,
City,
Province,
Region,
Distributor,
Opening_Date,
year(Opening_Date) as OpeningYear,
Start_Date,
Closing_Date,
year(Closing_Date) as ClosingYear,
MonthlyOutletRental,
"OutletSize(Sq meter)",
"# of shop assistants",
PayrollCost,
if(($(j)+1) > 24, 'Month 24+', 'Month'&($(j)+1)) as MonthNum,
date(date#('$(vMonth)','DD-MM-YYYY'),'DD-MM-YYYY') as MonthDate ,
date(Monthend(date#('$(vMonth)','DD-MM-YYYY')),'MMM YYYY') as MonthDateF,
year(date(date#('$(vMonth)','DD-MM-YYYY'),'DD-MM-YYYY')) as Year,
if(($(j)+1) > 0 and ($(j)+1) <=12, 'Outlet 12 Months',
if(($(j)+1) > 12 and ($(j)+1) <= 24, 'Outlet 13-24 Months',
if(($(j)+1) > 24 and ($(j)+1) <= 36, 'Outlet 25-36 Months',
if(($(j)+1) > 36 and ($(j)+1) <= 48, 'Outlet 37-48 Months',
if(($(j)+1) > 48 and ($(j)+1) <= 60, 'Outlet 49-60 Months',
if(($(j)+1) > 60 and ($(j)+1) <= 72, 'Outlet 61-72 Months',
if(($(j)+1) > 72 and ($(j)+1) <= 84, 'Outlet 73-84 Months',
if(($(j)+1) > 84 and ($(j)+1) <= 96, 'Outlet 85-96 Months',
if(($(j)+1) > 96 and ($(j)+1) <= 108, 'Outlet 97-108 Months',
if(($(j)+1) > 108, 'Outlet 108+ Months')))))))))) as YearNum,
1 as OpenCount,
if('$(vMonth)' = '$(vClosing_Date)',1,0) as CloseCount,
if($(j) = 0, 1,
if('$(vMonth)' = '$(vClosing_Date)',-1,0)) as OpenCloseCount,
if($(j) = 0, year(Opening_Date)&'0',
if('$(vMonth)' = '$(vClosing_Date)',year(Opening_Date)&year(Closing_Date),year(Opening_Date))) as YearConcat
Resident AllOutlets
where OutletID = '$(vOutletID)'
and City = '$(vCity)'
and Region = '$(vRegion)'
and Opening_Date = '$(vOpening_Date)';
vMonth = Date(MonthEnd(Date(AddMonths(vMonth,1),'DD-MM-YYYY')),'DD-MM-YYYY');
Next j;
Next i;
It will be better to explain the requirement so that we can suggest the better option to achieve the same
I think it's a great example of how it should not be done ... Even with small datasets it will run quite long times ...
The reason for it is you pick each single record from your source within an outside-loop, nest then another outside-loop to create multiple additionally records in regard to the periods and load then from the source with multiple condition in a where-clause which means that each time the whole source is accessed to check the conditions and to load the data with your other transformations.
I think the same could be reached by using an inside-loop with while - simplified something in that way:
load date(floor(addmonths(Date1, iterno() -1))) as Date1, [further fields ...]
from source while iterno() <= MonthDiff(Date1, Date2);
- Marcus