Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Auto load tables from MS SQL Database

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

error.png

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

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Auto load tables from MS SQL Database

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'); 

View solution in original post

4 Replies
Highlighted
Specialist II
Specialist II

Re: Auto load tables from MS SQL Database

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'); 

View solution in original post

Re: Auto load tables from MS SQL Database

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.

Highlighted
Specialist II
Specialist II

Re: Auto load tables from MS SQL Database

you can also try to Change this part

$(v_CurrentTableSchema)

where 1=1;

'$(v_CurrentTableSchema)'

where 1=1;

Highlighted

Re: Auto load tables from MS SQL Database

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...