Having problem while selecting multiple tables all at a time..

[nikhilgarg]

HEy,

When i choose any database (say having 50 tables)  and i have to select 15 tables in qlikview then i can not select them all at a time . I will have to select all 15 tables one by one. Is there any way to select all 15 tables all at a time ??

Please help.

Thanx.

[jagan]

Hi,

You have to use For loop to load the tables, check this link below

Load all tables in database dynamically

Hope it helps you.

Regards,

jagan.

[nikhilgarg]

Hey , Can you please help em understanding that script.

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

THanx in advance ...

[jagan]

Hi Nikhil,

The below statement loads all tables in database (Note this works for SQL Server)

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Let vTableCount = NoOfRows('SQLTableList'); - This will get the count of tables in database

This loops the table and reloads the tables dynamically

For i = 0 To $(vTableCount) -1 -- Loops 0 to n-1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']'; -- Gets i th table name in database using Peek() - Refer help file for detailed help

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', ''); - Genrates QVD name to be generated

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);  -- Loads the table from Database

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;  -- Stores the table into QVD

DROP Table $(vMyTableNameQVD); -- Drops the qvd

Next i

IF you don't want to delete the tables just remove this two lines below

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Hope this helps you.

Regards,

Jagan.

[nikhilgarg]

Hey,

Thanx for your reply. I get an overview but not completely.

WHat is  :

1: SQLTableList(Is this our defined variable if so then we give such name to only 1 table when loading but here we are loading multiple tables) ??

2: LOAD "TABLE_NAME" as  TableNames (What is TABLE_NAME  here ?? WHat does it points to ??)

3: FROM "INFORMATION_SCHEMA".COLUMNS; (What is "INFORMATION_SCHEMA".COLUMNS >> Is INFORMATION_SCHEMA somethign predefined ?? and what this ".Columns" does ??)

4: LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', ''); - Genrates QVD name to be generated.(I do not understand what is happening here ??)

5: STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;  -- Stores the table into QVD

DROP Table $(vMyTableNameQVD); -- Drops the qvd   (If we have to drop t he qvd's then why we are creating it at immediate previous step ??)

Please solve my doubts. I will be grateful to you.

Thanx.

[jagan]

Hi,

Use this script below to load the required tables, just change the table names below.  Replace Table1, Table2, Table3.....Tablen with your actual table names.

If you want detailed help regarding the functions refer qlikview help file or Qlikview reference manual file.

SQLTableList:

LOAD *

INLINE [

TABLE_NAME

Table1

Table2

Table3

Table4

'

'

'

'

Tablen];

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

Generally in companies if we need to load the same table data in many Dashboards then the QVDs are generated in one Qlikview and then reused this QVDs in other dashboard.  QVDs are removed to reduce the file size, we are deleting table because we are not using this in the current qlikview file.

Note: I suggest you to load the tables separately, instead of using loop. Because you have a better control, sometimes we need to filter data in some tables, or we need to restrict some columns.

Hope this helps you.

Regards,

Jagan.

[jagan]

For step 5: we are not dropping QVD, we are just dropping table in qlikview file, since we are not using it.

Regards,

Jagan.

[nikhilgarg]

HEy thanx jagan.

I get a glimpse of what this function trying to do but not completely. MAy be when i use it den i understand completely. ANyways thanx a lot

[nikhilgarg]

Hey,

Can you tell me that at the following step:

Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '')

Does replace function replacing each table name with a comma  ?? IS it so or something else ?/

Please help.

Thanx.

[jagan]

Hi Nikhi,

That statement replaces white spaces with empty values, simply removes whitespaces. 

Regards,

jagan.