Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Alternate solution would be to use IntervalMatch
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
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
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
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.
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;
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.
Interval match also will have same effect. It will run very slow and occupy more memory. You can try what swuehl suggested
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)