what is the fast way to extract large data

[#1]

Hi

I have a large table with 246 column .

When I try to extract all the data at once , it cause me a problem: it take long time and after a while it losing connection ( the server is in another country) .

I notice that if I take one year it work fast, so I am thinking to run per year and save it to QVD, then I will  union all years into one table via qlikview .

My question is :

I want to extract the data per year and I need it to run parallel way ( I do not to extract year 2007 and then year 2008 etc… )

How do I do that ?

Do I need to build qwv per year ?

Or maybe you have other solution ?

Thank you

[#2]

May be not ideal,  In the below Algo you can handled this task in one QVW.

1. Create a loop based on the year.

2. Open the DB connection inside the loop and pass the perticular year and fetch the data for that year and store it in QVD.

3. Once you stored the data in QVD you can disconnect the connection and reconnect and create next year QVD

[#3]

hi

thank you

but i dont think i realy understanding how to do that .

[#4]

SUB LoadTableData

FOR i=2007 to 2013

          MyTableName:

          SQL SELECT * FROM DBO.MyTableName;

          STORE MyTableName into          $(Directory)$(vMyTableName)$i.qvd;

NEXT i

EndSUB

CALL LoadTableData ;

Make sense ?

[#5]

hi

well yes , although i dont know where to write it .

any way it will not running parrallel.

[jagan]

Hi,

Try like this

Data:

Load

     Dim1,

     Dim2

AutoGenerate 1;

Years:   

Select DISTINCT Year(DateField) AS Year

FROM TableName;

LET vTotalRows = NoOfrows('Years');

//Loop through each row in Years table   

For i = 0 to vTotalRows - 1  

   LET vYear = Num(Peek('Year', $(i)));  //Get Year from table

    CONCATENATE(Data)

            SELECT

               Dim1,

               Dim2

            FROM TableName

            Where Year(DateField) = $(vYear);    

NEXT

Now you have all years data in Data table. Hope this helps you.

Regards,

Jagan.

[#7]

thank you

i will try it

[#8]

Yes, as per my understanding there is no parallel programming in Qlikview and it follows top to bottom approach.

[#9]

hi

what does the AutoGenerate do ??

do i have to use it ?

i read the hep and did not understand

[jagan]

Hi,

AutoGenerate is similar to looping, For example

Test:

Load

     Iterno() AS Dim1,

     Iterno() + 10 AS Dim2

AutoGenerate 10;

The above creates a table Test and inserts 10 rows with Dim1 and Dim2 dimensions.

Hope this helps you.

Regards,

Jagan.