Loading oracle table data into multiple qvd's

[vijetas42]

Hi All,

We have 26 million records in single oracle table and we want to divide and store that data into multiple qvd's based on row count.

Ex. row number 1 to 1000000 into QVD1 then 1000001 to 2000000 into QVD2 and so on.

any suggestions would be helpful if you have gone through same scenario.

Thanks!

@qlikview 

[rwunderlich]

Is that division by physical row number or something to do with an ID field?

If breaking up by physical row num, something like this. Instead of the sample Load to create the Data table, use your SQL statement like:

SQL Select * From db.mytable;

Data:
LOAD
  RecNo() as Id,
  rand() as Value
AutoGenerate 8000
;

Set vSize = 1000;
  For idx = 0 to Ceil(NoOfRows('Data') / vSize) - 1;
  Take:
  NoConcatenate First vSize
  Load *
  Resident Data
  Where RecNo() > $(idx) * $(vSize)
  ;
  Store Take into Take_$(idx).qvd (qvd);
  Drop Table Take;
Next idx;
Drop Table Data;  

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[vijetas42]

Thank you for your reply.

By using above code i just see ID and value is geting stored into qvd. where I need to store fields and their respective data from db table into different qvds

[rwunderlich]

The "Data" used in my example was meant to illustrate the concept. I meant for you to modify the script to use your own data and fields.  For example, replace the Load with a SQL select. 

-Rob

[vijetas42]

I am trying like this,

Data:
SQL select
RecNo() as Id,
USER_ID,
ACC_NBR
From Tab1;

Set vSize = 1000000;
For idx = 0 to Ceil(NoOfRows('Data') / vSize) - 1;
Take:
NoConcatenate First vSize
Load *
Resident Data
Where RecNo() > $(idx) * $(vSize)
;
Store Take into [$(zGlobalQVD_Location)\Take_$(idx).qvd] (qvd);
Drop Table Take;
Next idx;
Drop Table Data;

but, seems recno we can not use with sql script.

[rwunderlich]

RecNo() is a Qlik function. You can use it to add the field in a preceding load like this:

Data:
LOAD *,
  RecNo() as Id
;
SQL select
USER_ID,
ACC_NBR
From Tab1;

-Rob