topic Load database data using a loop in QlikView

Load database data using a loop

Mon, 16 Aug 2010 13:30:27 GMT

Hi,

I need to be able to load a database file using a loop on an ID. Our current load is taking too long and if I load an ID at a time it works quicker.

For example, I need to be able to load all the data from the table, but only the data associated by one field_id at a time - and then store all the data into a qvd.

I would be grateful for any help.

Thanks

Re. :Load database data using a loop

martin59 — Mon, 16 Aug 2010 13:33:33 GMT


FOR i = 2005 TO Year(Today())
Datas_$(i):
LOAD ....;
SQL SELECT ...
FROM MyTable
WHERE Year = $(i);
STORE Datas_$(i) into Datas_$(i).qvd;
DROP TABLE Datas_$(i);
NEXT</body>

Re. :Load database data using a loop

Mon, 16 Aug 2010 14:08:54 GMT

Hi Martin,

Thank-you for your reply.

What I need to be able to do is this but using a field ID - how do I get to do that as this wil increase each month, and will not be a date field.

Thanks again

Sally

Re. :Re: Re. :Load database data using a loop

martin59 — Mon, 16 Aug 2010 14:21:12 GMT

You have to know how much lines you will load

Max:
LOAD Max;
SQL SELECT Max(id) Max
FROM MyTable;
LET vMaxId = peek('Max');

You can specify your step

LET vStep = 10000; // number of rows per qvd

You have to create your loop

FOR i=0 TO $(vMaxId) STEP $(vStep)
Datas_First$(i):
LOAD *;
SQL SELECT *
FROM MyTable
WHERE id>$(i) AND id <=$(i)+$(vStep);
STORE Datas_First$(i) INTO Datas_First$(i).qvd;
DROP TABLE Datas_First$(i);
NEXT

Don't try this code, but it must be something like that