using for next loop taking loading time 6 hrs

Omkarsv2891 — Wed, 26 Aug 2020 12:33:18 GMT

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;

Re: using for next loop taking loading time 6 hrs

Kushal_Chawda — Wed, 26 Aug 2020 12:42:29 GMT

It will be better to explain the requirement so that we can suggest the better option to achieve the same

Re: using for next loop taking loading time 6 hrs

marcus_sommer — Wed, 26 Aug 2020 14:30:10 GMT

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

topic Re: using for next loop taking loading time 6 hrs in App Development

using for next loop taking loading time 6 hrs

Re: using for next loop taking loading time 6 hrs

Re: using for next loop taking loading time 6 hrs