topic Re: Loading oracle table data into multiple qvd's in QlikView

Loading oracle table data into multiple qvd's

vijetas42 — Wed, 13 Apr 2022 17:23:22 GMT

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

Re: Loading oracle table data into multiple qvd's

rwunderlich — Wed, 13 Apr 2022 21:45:46 GMT

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

Re: Loading oracle table data into multiple qvd's

vijetas42 — Mon, 18 Apr 2022 08:41:57 GMT

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

Re: Loading oracle table data into multiple qvd's

rwunderlich — Mon, 18 Apr 2022 14:29:49 GMT

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

Re: Loading oracle table data into multiple qvd's

vijetas42 — Mon, 18 Apr 2022 14:47:04 GMT

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.

Re: Loading oracle table data into multiple qvd's

rwunderlich — Mon, 18 Apr 2022 14:53:20 GMT

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