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

[sunny_talwar]

Alternate solution would be to use IntervalMatch

[mvanlutterveld]

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

[#4]

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

[#5]

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]

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.

[Kushal_Chawda]

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

[Kushal_Chawda]

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)