While loop with iterno() taking 40 minutes

[rarora12]

load  link_Sup_ADCNO,  BeginningDateofSupervision + IterNo()-1 as Dup_analysis_date , S as Dup_S
from E:\Qlik\Development\2_Staging\QVD\Supervision.qvd(qvd)
     while IterNo() <= Supervision_End_Date - BeginningDateofSupervision +1 ;

THis statement goes through about 60,000,000 adcno with date ranges starting from 1985 to 2016 but its taking about 40 minutes.

Is there anything I can do for limiting or tuning this data?

I cant use interval match or set analysis as I need these analysis dates for the dashboard

I am using a calendar and I need to create each date

THanks in advance to all who can assist

Rick

[oknotsen]

The while clause you are using results in the QVD not being loaded "optimized". As a result, it goes a lot slower then needed.

Two options:

1. Load the whole QVD and do a resident load on that table with the where clause. Don't forget to use "noconcatenate" and drop the initial table.
2. Create a flag field that only contains 0's and 1's when the QVD gets created. When loading the QVD, use "where exists(nameOfFlagField). This will result in an optimized load, which goes a lot faster.
   

[martinpohl]

Hello,

dön't know exactly how to use with your data, but as an idea:

load date($(vMinDate)+rowno()-1) as Date

AutoGenerate ($(vMaxDate)-$(vMinDate)+1);   

rowno instaead of iterno

autogenerate instead of do while

Regards

[rarora12]

Appreciate both ..will try both..

Question ..Onnno please provide example of flag field ,what field to create flag on ?

Thanks again

[oknotsen]

If you could explain what you are trying to calculate with this, I might be:

IterNo() <= Supervision_End_Date - BeginningDateofSupervision +1 ;

[rarora12]

Its a while loop so the statement keeps looping with iterno()  until the counter is met . Counter is provided by the  Supervision_End_Date - BeginningDateofSupervision +1.

Thanks

[rarora12]

Martin

I believe I am not doing something right as the rowno() option doesn't work when I have a table name in the load statement.

Can you assist in the syntax?

[rarora12]

getrange:
LOAD link_Sup_ADCNO, BeginningDateofSupervision ,  Supervision_End_Date ,S,Supervision_End_Date - BeginningDateofSupervision +1 as counter
from  E:\Qlik\Development\2_Staging\QVD\Supervision.qvd(qvd)
;
NoConcatenate

    
Sup_link:

load  link_Sup_ADCNO,  BeginningDateofSupervision + IterNo()-1 as Dup_analysis_date  , S as Dup_S   Resident getrange
while IterNo()-1<= counter ; 
drop table getrange;

I am using the idea about the resident table and I am back to the performance being bad - its running again for more than 30 minutes

Any other ideas

Thanks

Rick