Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

25 Replies
sunny_talwar

Alternate solution would be to use IntervalMatch

mvanlutterveld
Partner
Partner

Hi Sasi,

Check if field End_Date has a (legit) value. The While runs from Start_Date to End_Date. If End_Date is empty or has date values like 31-12-2999 your While will run for ages. If so, use the Intervalmatch as Sunny suggested or add extra RAM to the server.

If End_Date is empty or has extreme values you can set your own End_Date value based on Today() or whatever meets your requirements.

Cheers,

Michiel

Not applicable
Author

Thanks Sunny.

More than 10000 Certificates & Years of Data, it is taking long time.Also consumes more than 400 MB.

Will IntervalMatch function  resolves this issue.

Can you please suggest me.

Thanks

Not applicable
Author

Thanks Sunny, Michiel.

Now my code like below,

Premium_Calc:

LOAD

Client_DS,

Certificate_No,

Start_Date,

End_Date,

Date(Start_Date+iterno()-1) as Date,

Premium_Amt

FROM

(ooxml, embedded labels, table is Sheet1)

while Start_Date+iterno()-1 <= End_Date;

Please suggest me how to modify with IntervalMatch?

Thanks

sunny_talwar

There is no way to confirm, but you can always test run it to check if IntervalMatch is faster or not. The benefit I see is that you can keep IntervalMatch as a separate table (which will form a synthetic key, but is not a bad things as per HIC- > IntervalMatch) and can run the application a little faster.

sunny_talwar

May be something like this:

Premium_Calc:

LOAD Client_DS,

          Certificate_No,

          Start_Date,

          End_Date,

          Premium_Amt

FROM

(ooxml, embedded labels, table is Sheet1);

MinMax:

LOAD Min(Start_Date) as MinDate,

          Max(End_Date) as MaxDate

Resident Premium_Calc;

LET vMinDate = Peek('MinDate');

LET vMaxDate = Peek('MaxDate');

Calendar:

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

AutoGenerate 1

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

IntervalMatch:

IntervalMatch(Date)

LOAD Start_Date,

          End_Date

Resident Premium_Calc;

swuehl
MVP
MVP

I don't think that you will see a great benefit from using an IntervalMatch here.

I think what you are trying to do is similar to what is described here:

Creating Reference Dates for Intervals

Even with a thousand certificates and some year date range each, the script should not run forever nor should it use a lot of memory, I assume just like Michiel that your end dates are not appropriate in all cases.

Besides that, I think your current expression:

=If (MonthYear < $(vFiscalStartDt),0,

If (MonthYear > $(vFiscalEndDt),0,

(sum( distinct Premium_Amt)/365) * Count( DISTINCT Date)* Count(distinct Certificate_No)))

will not return correct results, at least for partial sums. Maybe it would be better to calculate the premium amount share per date and certificate in the script, when creating the reference dates.

Kush
MVP
MVP

Interval match also will have same effect. It will run very slow and occupy more memory. You can try what swuehl‌ suggested

Kush
MVP
MVP

Meanwhile you can try below. Check the New Calculation expression in screenshot which is without Date.

Try to use the expression without iterno() and check if it works

Earned Premium

If (MonthYear < $(vFiscalStartDt),0,
If (MonthYear > $(vFiscalEndDt),0,
(
sum(distinct Premium_Amt)/365)*

((if(End_Date>=monthstart(MonthYear) and End_Date<=monthend(MonthYear),Floor(End_Date),Floor(monthend(MonthYear)))
-

if(Start_Date < monthstart(MonthYear),Floor(monthstart(MonthYear)),Floor(Start_Date)))+1)*Count(Distinct Certificate_No)))

EP with Certificate

= (sum(distinct Premium_Amt)/365)*

((if(End_Date>=monthstart(MonthYear) and End_Date<=monthend(MonthYear),Floor(End_Date),Floor(monthend(MonthYear)))
-

if(Start_Date < monthstart(MonthYear),Floor(monthstart(MonthYear)),Floor(Start_Date)))+1)

EP without Certificate

(sum(distinct Premium_Amt)/365)*

((if(End_Date>=monthstart(MonthYear) and End_Date<=monthend(MonthYear),Floor(End_Date),Floor(monthend(MonthYear)))
-

if(Start_Date < monthstart(MonthYear),Floor(monthstart(MonthYear)),Floor(Start_Date)))+1)*

Count(Distinct Certificate_No)