topic Re. :Re: Re. :Creating a qvd file per for each table in an Oracle database in QlikView

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

Anonymous — Tue, 19 Apr 2011 10:21:19 GMT

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?

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

sushil353 — Tue, 19 Apr 2011 10:29:17 GMT

try

store maximo_load_script into filename.qvd(qvd)

HTH

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

vincent_ardiet — Tue, 19 Apr 2011 10:33:54 GMT

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

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

Anonymous — Tue, 19 Apr 2011 10:47:36 GMT

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

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

ashfaq_haseeb — Tue, 19 Apr 2011 10:56:11 GMT

Hi jmoellerhoej

Its just inline load. Seee the application attached

Just to show you demo.

Hope that help.

Regards

ASHFAQ

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

vincent_ardiet — Tue, 19 Apr 2011 11:07:58 GMT

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;

Regards,

Vincent

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

matt_crowther — Tue, 19 Apr 2011 11:21:12 GMT

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

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

Anonymous — Tue, 19 Apr 2011 11:53:01 GMT

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;