topic Re: Load Script with variables in QlikView

Load Script with variables

Fri, 12 Aug 2016 12:34:24 GMT

Hi,

I have a database with many tables. I started to load the list of sqltables and filtered them.

Now I want to load in the script many tables at once without scriptcode for every single table.

After Loading I want to store them in a Directory as csv.

For that I want to create a variable, that gives me back the Table_SCHEMA-entry (CODA)

Additionally I wanted to create a second variable (varTableName), that give me the TableName-entry (efaEFA_ADOCTMP, EFA_ASSET and so on)

I tried this to load automatically:

LET DirDataQVD = 'F:\CODA\';

Tmp_sqltables:
Load *;
SQLTABLES;

sqltables:
Load
TABLE_SCHEMA,
TABLE_TYPE,
TABLE_NAME
resident Tmp_sqltables
where TABLE_SCHEMA = 'CODA' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'EFA*' ;
drop table Tmp_sqltables;

Let vTableName = TABLE_NAME;
Let vTableScheme = TABLE_SCHEMA;

// ------------------------------------------------------------------------------------------

$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);

store $(vTableScheme) into $(DirDataQVD)$(vTableName).csv (txt, delimiter is ';');
drop Table $(vTableName);

But unfortunately it is not working
(TABLE_NAME.TABLE_NAME is wrong, correct would be FROM CODA.EFA_ASSET)

How can I realise this? (please see attached my file)

very much thanks in advance.

Chris

Re: Load Script with variables

Gysbert_Wassenaar — Fri, 12 Aug 2016 12:40:36 GMT

You need to use a loop to iterate through the Temp_sqltables table and for each row use the peek function to retrieve the field values and store them in the variables.

You can find an example here that does the same with excel sheets, which are also tables retrieved with the SQLTABLES command: Re: Loading Multiple Excel Files and Multiple Excel sheets

Re: Load Script with variables

sunny_talwar — Fri, 12 Aug 2016 12:41:22 GMT

I think you will need to use Peek() function to store the value of your table_name and table_schema into a variable. You cannot just do LET vVar = table_name

Re: Load Script with variables

Mon, 15 Aug 2016 07:15:15 GMT

Hi you both,

that sounds good. Would you please help me with that?

I do not get it working.

Thanks.

Chris

Re: Load Script with variables

crusader_ — Mon, 15 Aug 2016 09:01:16 GMT

Hi Christian,

First of all looking into your script and error message you have a typo.

You need to put varTableSchema instead of varTableName in below statement.

$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);

Fix it and try other suggestions with loop and Peek() function.

Hope this helps.

Andrei

Re: Load Script with variables

maxgro — Mon, 15 Aug 2016 09:49:01 GMT

ODBC CONNECT...........

LET DirDataQVD = '.'; // use your folder

Tmp_sqltables:

Load *;

SQLTABLES;

sqltables:

Load

TABLE_SCHEM,

TABLE_TYPE,

TABLE_NAME,

TABLE_CAT

resident Tmp_sqltables

where TABLE_CAT = 'sisim_stat' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'd*'

; // replace with your schema, table_name

drop table Tmp_sqltables;

for i=0 to NoOfRows('sqltables')-1

Let vTableName = peek('TABLE_NAME', $(i), 'sqltables');

Let vTableCat = peek('TABLE_CAT', $(i), 'sqltables');

Let vTableScheme = peek('TABLE_SCHEM', $(i), 'sqltables');

trace vTableName=$(vTableName); // for test

trace vTableCat=$(vTableCat);

trace vTableScheme=$(vTableScheme);

$(vTableName):

SQL SELECT top 10 * // remove top 10

FROM $(vTableCat).$(vTableScheme).$(vTableName);

store $(vTableName) into [$(DirDataQVD)\$(vTableName).csv] (txt, delimiter is ';');

drop Table $(vTableName);

NEXT