topic Re: Dynamic function to load all table in QlikView

Dynamic function to load all table

Mon, 23 May 2016 08:21:34 GMT

Hello All,

I have multiple table in a database:

TEST_2015_01

TEST_2015_02

TEST_2015_03

TEST_2015_04

TEST_2015_05

TEST_2015_06

TEST_2015_07

I need to load all of them in a single table.

I am trying the below but it does not seem to work:

SQL SELECT *

FROM "ABCD_BKP".dbo.TEST_*;

Next

Any ideas?

Kind Regards,

Hasvine

Re: Dynamic function to load all table

swuehl — Mon, 23 May 2016 08:28:41 GMT

You can use the SQLTABLES SQL function to retrieve a list of your tables in your DB. Then iterate over the tables names using FOR ... NEXT

Have a look at your DBMS manual, there are also examples here in the forum how to do this in QV script.

Re: Dynamic function to load all table

Kushal_Chawda — Mon, 23 May 2016 08:30:07 GMT

Tables:

LOAD * inline [

Name

TEST_2015_01

TEST_2015_02

TEST_2015_03

TEST_2015_04

TEST_2015_05

TEST_2015_06

TEST_2015_07 ];

Final:

LOAD * inline [

Junk ];

for i=1 to fieldvaluecount('Name')

let vName = fieldvalue('Name',$(i));

concatenate(Final)

LOAD * ;

SQL SELECT *

FROM "ABCD_BKP".dbo.$(vName)";

NEXT i

drop field Junk;

Store Final into ..\Final.QVD;

Re: Dynamic function to load all table

Mon, 23 May 2016 08:33:54 GMT

Hi Kushal,

I used to do that but the problem is that every month a new table is created in the database.

We have already reached like table TEST_2016_04

I need a dynamic way to do it.

Many Thanks,

Hasvine

Re: Dynamic function to load all table

Kushal_Chawda — Mon, 23 May 2016 08:45:32 GMT

I have generated Table Name from Jan 2015 to Current month

Table:

LOAD 'TEST_' & date(Monthstart(Date),'YYYY_MM') as Name;

LOAD makedate(2015,1,1)+iterno()-1 as Date

Autogenerate 1

While makedate(2015,1,1)+iterno()-1 <= today();

Final:

LOAD * inline [

Junk ];

for i=1 to fieldvaluecount('Name')

let vName = fieldvalue('Name',$(i));

concatenate(Final)

LOAD *,

'$(vName)' as TableName;

SQL SELECT *

FROM "ABCD_BKP".dbo.$(vName)";

NEXT i

drop field Junk;

Store Final into ..\Final.QVD;