IterNo() occupies memory & time when working with volume of records

[#1]

Dear QVExperts,

The enclosed qvw splits the Premium into number of months/days.

We used IterNo() to distinguish the dates & Distinct Count of the date provides the number of days.

But when working with large volume of transactions, it is taking long time & finally QlikView Error.

Any alternate solutions... Please suggest.

Thanks,

Sasi

[swuehl]

Well there is a difference between 10000 certificates (as stated before) and 4 million.

What is the average time period you need to create for a certificate?

[#3]

Swuehl,

For one client it is around 10000 Certificates. But we have several clients.

Total Certificates are around 4 millions

[#4]

Thanks Kush.

[#5]

Thanks Sunny.

LOAD Date($(vMinDate) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

The above portion of script taking long time & consumes memory.

We will know the Fiscal Start Date & Fiscal End Date only when the user enters.

Can we reload based on the Date Range Entries by user?

Please suggest me.

Thanks

[#6]

In my experience, in this particular case IterNo() is much more faster than any approach like IntervalMatch or Loops. But if you add too many precedent loads it may impact the performance.

The other approach is make the script more incremental fashion with end date value changes with some setup QVD's to identify the change.

[swuehl]

johnw‌ recently demonstrated a nice approach how you can kind of 'compress' the amount of lines per contract needed:

While loop for creating dates between 1985 and 2016 - performance issues