Using "Load First n" in a loop

[imac]

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!   

[marcus_sommer · Accepted solution]

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)];

next

drop tables RawDataFlag, Last3, Last10;

- Marcus

[imac]

Perfect! Thanks Marcus, works like a charm!!