Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
imac
Contributor II
Contributor II

Using "Load First n" in a loop

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!   

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

Perfect! Thanks Marcus, works like a charm!!