topic Re: Auto load tables from MS SQL Database in QlikView

Auto load tables from MS SQL Database

slaheddinekeyru — Mon, 29 Jan 2018 13:18:37 GMT

Hello community,

I'm trying to load data from a database using an Excel file in wich I define the tables and fields names. Then I use a loop to load data into my qvw app.

I get a result for the first table but I get an error when the loop moove te the second table.

Here's the error

And here's the script :

//Récupération de la liste des tables à charger à partir de la base
tmp_TablesNames:
LOAD 
 Distinct
 [Table Name]
FROM
 [$(p_Path_Package)\Data\QV_External_Table_Parameters.xlsx]
 (ooxml, embedded labels, table is TABLES);


//Calcul du numbre de tables à parcourir
LET v_TablesNumber = NoOfRows('tmp_TablesNames');


//Boucle de chargement des tables
FOR i=0 TO $(v_TablesNumber)-1


 LET v_CurrentTableName = SubField(Peek('Table Name',$(i)),'.',-1);
 LET v_CurrentTableSchema = Peek('Table Name',$(i));


 TRACE v_CurrentTableName = $(v_CurrentTableName);

 $(v_CurrentTableName):
 LOAD
 *;
 SQL
 Select
 *
 From
 $(v_CurrentTableSchema)
 where 1=1;


NEXT i



//Suppression de la table temporaire tmp_TablesNames
DROP Table tmp_TablesNames;

Thanks in advance

Re: Auto load tables from MS SQL Database

zhadrakas — Mon, 29 Jan 2018 13:24:22 GMT

check your peek function calls. it should look like that:

Peek(field_name[, row_no[, table_name ] ] )

give that a try:

Peek('Table Name',$(i), 'tmp_TablesNames');

Re: Auto load tables from MS SQL Database

Peter_Cammaert — Mon, 29 Jan 2018 13:29:29 GMT

Check the output of the TRACE statement (immediately before the error occurs). Does variable v_CurrentTableName contain a correct value? Because the $-sign expansion doesn't find one.

Re: Auto load tables from MS SQL Database

zhadrakas — Mon, 29 Jan 2018 13:29:51 GMT

you can also try to Change this part

$(v_CurrentTableSchema)

where 1=1;

'$(v_CurrentTableSchema)'

where 1=1;

Re: Auto load tables from MS SQL Database

Peter_Cammaert — Mon, 29 Jan 2018 13:37:51 GMT

Tim is right. The first extraction may work, as you are referring in your peek() function calls to the (default) table that was loaded last. But as soon as there is another resident table (the first one extracted from SQL Server), your peek() function calls will use a different default table that has no [Table Name] column and the calls will return NULL...