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

[Kushal_Chawda]

I think there is mistake in my understanding. MonthYear is calculated on Date only. Let me check some other option

[#3]

Thanks swuehl & kush,

Please find the updated input sheet.

[swuehl]

Ok, what's the issue with that file?

I've attached a sample with a daily Amount, which should make your aggregation much easier.

[Kushal_Chawda]

I have tried another method of Cross Join. I am not sure this method is optimal or not but you can try

Replace inline table with your data. I have used Date# but while loading from your source if Date are in proper format then no need to use Date#

Data:
LOAD Client_DS,
Certificate_No,
if(isnum(Start_Date),date(Start_Date),date(Date#(Start_Date,'MM/DD/YYYY'))) as Start_Date,
if(isnum(Start_Date),date(End_Date),date(Date#(End_Date,'MM/DD/YYYY'))) as End_Date,
//End_Date,
    Premium_Amt,
Claim_Open_Date,
Claim_Amt
FROM
C:\Users\kush\Downloads\Premium_Sample.xlsx
(ooxml, embedded labels, table is Sheet1);

New:
LOAD distinct
date(Start_Date) as Date
Resident Data;

Concatenate
LOAD distinct
Date(End_Date) as Date
Resident Data;

MaxMinDate:
LOAD min(Date) as MinDate,
max(Date) as MaxDate
Resident New;

DROP Table New;

let vMinDate= Peek('MinDate',0,'MaxMinDate');
let vMaxDate= Peek('MaxDate',0,'MaxMinDate');

Join(Data)
LOAD date('$(vMinDate)'+IterNo()-1) as Date1
AutoGenerate 1
While '$(vMinDate)'+IterNo()-1 <= '$(vMaxDate)';

Final:
NoConcatenate
LOAD *,
date(MonthStart(Date1),'MMM YYYY') as MonthYear
Resident Data
where Date1 >= Start_Date and Date1 <= End_Date;

DROP Table Data;
 

Doubt-

If you have 10000 certification number and 730 Dates(2 years) then 10000*730 rows will be created due to cross join at this point only performance concern may come. Further we are reducing the rows in where which I don't think there will be any issue.

swuehl‌ can better tell about this method.

[#6]

Thanks a lot swuehl & kushal.

[#7]

Swuehl & Kushal,

Both the options are taking time & out of memory.

Please suggest me.

Thanks,

Sasi

[swuehl]

As already mentioned, I think this has something to do with your input data for end and start date.

I've noticed that some records in your Excel table showed different format for the dates.

I assume that with your full and real data set, some dates are not interpreted correctly.

Try to read your table without the WHILE loop. Then check both dates field, that all records are showing correct numeric representations of the dates. Also check that date intervals are realistic and not implying several thousand of years or something like this.

Why don’t my dates work?

Get the Dates Right

[Kushal_Chawda]

How much is the RAM? Are you running application on local machine or on Server?

[#10]

Kush - 16GB RAM. Around 4 million records [ certificates]

[Kushal_Chawda]

I would suggest you to perform this loop ( Iterno() ) logic at database level and create the View for the same. Then take that data from view into the QlikView.