Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Creating a qvd file per for each table in an Oracle database

Hello,

We have just started using Qlikview in our organisation and I am struggling a bit with a load script

What is working is :

run a piece of sql to show all the tables owend by a certain user and create a string for each

The script is a follows

ODBC CONNECT TO.......

MAXIMO_LOAD_SCRIPT:

LOAD *;

SQL SELECT 'MAXIMO.'|| table_name ||':' || ' '|| 'LOAD * ; SQL SELECT * FROM' ||' '|| 'MAXIMO.'|| table_name ||';' ||' ' || 'STORE'||' ' || 'MAXIMO.'|| table_name||' '||'INTO C:\TEST\MAXIMO_QVD\MAXIMO.'||TABLE_NAME||'.QVD'||';' AS LOAD

FROM ALL_TABLES

where owner = 'MAXIMO'

and table_name not like '%$%'

AND TABLE_NAME IN ('ASSET','ACTION');



The output of the above is

MAXIMO.ACTION: LOAD * ; SQL SELECT * FROM MAXIMO.ACTION; STORE MAXIMO.ACTION INTO C:\TEST\MAXIMO_QVD\MAXIMO.ACTION.QVD;

MAXIMO.ASSET: LOAD * ; SQL SELECT * FROM MAXIMO.ASSET; STORE MAXIMO.ASSET INTO C:\TEST\MAXIMO_QVD\MAXIMO.ASSET.QVD;

Where I am struggling is the next step.

I want pass these values into a new file which can be run on a schedule and the idea is that all the specified tables will be extracted as qvd files

is this even possible?

1 Solution

Accepted Solutions
vincent_ardiet
Specialist
Specialist

I write for you something quick, this is not bug free (not tested) :

ODBC CONNECT TO.......

MAXIMO_LOAD_SCRIPT:

LOAD table_name;

SQL SELECT table_name FROM ALL_TABLES

where owner = 'MAXIMO'

and table_name not like '%$%'

AND TABLE_NAME IN ('ASSET','ACTION');

LET vRowNb = NoOfRows('MAXIMO_LOAD_SCRIPT')-1;

for vLoop = 0 to $(vRowNb)

let vTable = Peek('table_name',$(vLoop));

temp_table:

LOAD * ; SQL SELECT * FROM MAXIMO.$(vTable);

STORE temp_table INTO C:\TEST\MAXIMO_QVD\MAXIMO.$(vTable);

DROP TABLE temp_table;

next

Regards,

Vincent



View solution in original post

7 Replies
sushil353
Master II
Master II

try

store maximo_load_script into filename.qvd(qvd)

HTH

vincent_ardiet
Specialist
Specialist

Hi,

Perhaps you can try with those steps :

- Load the all_table data in a QV table ;

- Write a loop (using FOR) to iterate on the all_tables records ;

- Using the PEEK function to set variables, read row after row and each time you can make a LOAD and a STORE based on those variables.

Regards,

Vincent

Anonymous
Not applicable
Author

Sorry to be a pain could you do me an example ?

ashfaq_haseeb
Champion III
Champion III

Hi jmoellerhoej

Its just inline load. Seee the application attached

Just to show you demo.

Hope that help.

Regards

ASHFAQ

vincent_ardiet
Specialist
Specialist

I write for you something quick, this is not bug free (not tested) :

ODBC CONNECT TO.......

MAXIMO_LOAD_SCRIPT:

LOAD table_name;

SQL SELECT table_name FROM ALL_TABLES

where owner = 'MAXIMO'

and table_name not like '%$%'

AND TABLE_NAME IN ('ASSET','ACTION');

LET vRowNb = NoOfRows('MAXIMO_LOAD_SCRIPT')-1;

for vLoop = 0 to $(vRowNb)

let vTable = Peek('table_name',$(vLoop));

temp_table:

LOAD * ; SQL SELECT * FROM MAXIMO.$(vTable);

STORE temp_table INTO C:\TEST\MAXIMO_QVD\MAXIMO.$(vTable);

DROP TABLE temp_table;

next

Regards,

Vincent



matt_crowther
Luminary Alumni
Luminary Alumni

This does what you're after but against a SQL Database - Load all (or selected) tables and store them to QVD:

Password for hidden script is '1234'.

http://community.qlik.com/media/p/154056.aspx

Hope it's of use,

Matt - Visual Analytics Ltd

Anonymous
Not applicable
Author

You sir are a wizard!

After a bit of tweaking i got it working as I wanted (only problem was the lower case table_name)

many Thanks!!



MAXIMO_LOAD_SCRIPT:

LOAD

TABLE_NAME

;

SQL

SELECT table_name FROM ALL_TABLES

where owner = 'MAXIMO'

and table_name not like '%$%'

AND TABLE_NAME IN ('ASSET','ACTION');



LET

vRowNb = NoOfRows

('MAXIMO_LOAD_SCRIPT')-1;

for

vLoop = 0 to

$(vRowNb)

let

vTable = Peek('TABLE_NAME',$(vLoop)

);

temp_table:



LOAD

* ; SQL SELECT * FROM MAXIMO. $(vTable)

;

STORE

temp_table INTO C:\TEST\MAXIMO_QVD\MAXIMO. $(vTable)

;

DROP

TABLE

temp_table;

next