Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jmoellerhoej
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
Not applicable

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

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



7 Replies
sushil353
Not applicable

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

try

store maximo_load_script into filename.qvd(qvd)

HTH

vincent_ardiet
Not applicable

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

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

jmoellerhoej
Not applicable

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

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

ashfaq_haseeb
Not applicable

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

Hi jmoellerhoej

Its just inline load. Seee the application attached

Just to show you demo.

Hope that help.

Regards

ASHFAQ

vincent_ardiet
Not applicable

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

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
Not applicable

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

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

jmoellerhoej
Not applicable

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

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