topic Re: Using "Load First n" in a loop in QlikView

Using "Load First n" in a loop

imac — Wed, 04 Jul 2018 14:49:52 GMT

Hi everyone

Here is the scenario.

I have a large table with a list of invoices structured somewhat like this:

Company, Invoice Number, Payment Period, Amount

What I need is to use this base table and create two more tables, one that has the last 3 invoices by customer, and another that has the last 10 invoices by customer. The two tables need to be exported for use elsewhere (not within QW)

The way I am attempting to do this now (which works for the most part) is in a for.next loop, something like this (pseudocode)

CompanyTable:

Load distinct Company from Table;

For i = 1 to numrows()

Let vCompany = Peek(Company, i)

Last3:

First 3 Load Company, Invoice Number, Payment Period, Amount where Company = vCompany order by Payment Period desc;

Last10:

First 10 Load Company, Invoice Number, Payment Period, Amount where Company = vCompany order by Payment Period desc;

next;

store Last3 into Last3.txt;

store Last10 into Last10.txt;

As I said, for the most part it gives me what I want but takes forever as the main table has a few million records, and I am sure there must be a better way of accomplishing the same thing.

Any advice appreciated!

Re: Using "Load First n" in a loop

marcus_sommer — Thu, 05 Jul 2018 07:50:13 GMT

You could try something like this to speed up your task:

RawDataFlag:

load

Company, [Invoice Number], [Payment Period], Amount,

-(autonumber(rowno(), Company)<=3) as Last3,

-(autonumber(rowno(), Company)<=10) as Last10,

Resident RawData order by Company, [Payment Period] desc;

Last3:

noconcatenate load Company, [Invoice Number], [Payment Period], Amount

resident RawDataFlag where Last3 = 1;

Last10:

noconcatenate load Company, [Invoice Number], [Payment Period], Amount

resident RawDataFlag where Last10 = 1;

for each vCompany in fieldvaluelist('Company')

[$(vCompany)]:

noconcatenate load * resident Last3 where Company = '$(vCompany');

store [$(vCompany)] into [$(vCompany)_Last3].txt (txt);

drop tables [$(vCompany)];

[$(vCompany)]:

noconcatenate load * resident Last10 where Company = '$(vCompany');

store [$(vCompany)] into [$(vCompany)_Last10].txt (txt);

drop tables [$(vCompany)];

drop tables RawDataFlag, Last3, Last10;

- Marcus

Re: Using "Load First n" in a loop

imac — Sun, 08 Jul 2018 09:49:44 GMT

Perfect! Thanks Marcus, works like a charm!!