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

[Anonymous]

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?

[sushil353]

try

store maximo_load_script into filename.qvd(qvd)

HTH

[vincent_ardiet]

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]

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

[ashfaq_haseeb]

Hi jmoellerhoej

Its just inline load. Seee the application attached

Just to show you demo.

Hope that help.

Regards

ASHFAQ

[vincent_ardiet · Accepted solution]

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]

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]

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