Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Omkarsv2891
Partner
Partner

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
MVP & Luminary
MVP & Luminary

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