topic Re: How to load from variable table name in QlikView

How to load from variable table name

Fri, 27 Dec 2013 22:13:58 GMT

Hi,

My table name will not always be the same and there is no pattern to the name. How do I get the load script to pull the data? Your help is much appreciated.

Re: How to load from variable table name

maxgro — Fri, 27 Dec 2013 23:26:25 GMT

are you looking for a way to load multiple table from same db? In that case try this post

http://community.qlik.com/message/191708#191708

Regards

Re: How to load from variable table name

stevedark — Fri, 27 Dec 2013 23:42:30 GMT

Hi Lanthy,

At some point you must know what the table name is in order to be able to load from it?

You can use a variable in your load statement like this:

LOAD
*

;

SQL SELECT
*

FROM $(vTableName)

;

You could then have an input box on the screen to allow the user to input the table name before refreshing?

If the user does not know the table name, but you know the database it is in you could perhaps get a list of tables in the database (perhaps doing a select on SysObjects (in SQL Server) or by using the SQLTABLES command (for ODBC connections) and then use a PEEK statement to get the table name from that query into a variable before performing a load as above.

If you could elaborate on how you would envisage the table name should be derived or captured then I can give further advice on how to achieve that.

- Steve

Re: How to load from variable table name

Mon, 30 Dec 2013 17:59:43 GMT

Hi Steve,

My script looks like this:

tempload:

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

The file name will be known, but the the table name is unknown....Sheet1 will not always be named Sheet1 and I will not be able to predict the table name because it is a randomly generated long set up letters and numbers by an external source.

Re: How to load from variable table name

Mon, 30 Dec 2013 18:00:18 GMT

Massimo,

That's not exactly what i'm trying to do, but i will bookmark it for future use! Thanks for your help!

Re: How to load from variable table name

stevedark — Mon, 30 Dec 2013 18:52:42 GMT

Hi,

Take a look at this thread:

http://qlikcommunity.qliktech.com/thread/18641

You can simply replace Sheet1 with @1 and it will load the first sheet.

Hope that helps.

Steve

Re: How to load from variable table name

Mon, 30 Dec 2013 19:05:33 GMT

Steve,

@1 doesnt work for xlsx files, but per the another post on the same thread removing 'table is ___' works!
So my script was:

(ooxml, embedded labels, table is Sheet1);

and updated to the below which works:

(ooxml, embedded labels);

Thanks for pointing me to that post!

Re: How to load from variable table name

stevedark — Mon, 30 Dec 2013 21:04:42 GMT

Glad that works for you. Another approach I have used is to create an ODBC

connection to the spreadsheet and then use SQLTables to list all sheets.

You can then PEEK these into variables. This is good for when the are

multiple sheets you want to load.

Cheers,

Steve