Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Omkarsv2891
Partner - Contributor
Partner - Contributor

using for next loop taking loading time 6 hrs

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;

2 Replies
Kushal_Chawda

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

marcus_sommer

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