topic Re: Having problem while selecting multiple tables all at a time.. in QlikView

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

nikhilgarg — Thu, 04 Sep 2014 06:05:08 GMT

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.

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

jagan — Thu, 04 Sep 2014 06:09:03 GMT

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.

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

nikhilgarg — Thu, 04 Sep 2014 06:14:09 GMT

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

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

jagan — Thu, 04 Sep 2014 06:29:11 GMT

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.

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

nikhilgarg — Thu, 04 Sep 2014 06:47:30 GMT

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.

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

jagan — Thu, 04 Sep 2014 06:58:03 GMT

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.

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

jagan — Thu, 04 Sep 2014 06:59:06 GMT

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

Regards,

Jagan.

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

nikhilgarg — Thu, 04 Sep 2014 07:08:33 GMT

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

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

nikhilgarg — Thu, 04 Sep 2014 07:16:01 GMT

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.

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

jagan — Thu, 04 Sep 2014 08:13:48 GMT

Hi Nikhi,

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

Regards,

jagan.

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

nikhilgarg — Thu, 04 Sep 2014 08:53:24 GMT

Ohk thanx But why do we need to create it in separate variable. We can do this in a step above also , right ??

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

jagan — Thu, 04 Sep 2014 08:58:48 GMT

Hi,

one for getting actual database table name and another for QVD name.

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

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

Regards,

Jagan.

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

nikhilgarg — Thu, 04 Sep 2014 09:11:13 GMT

yOu mean to say that we use qvd's bcoz they contain data in reduced form. So we are removing it in second step. Although we can do it in previous step also but no required. Right ??

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

We are fetching table name that contains data but with no spaces or commas . Right ??

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

jagan — Thu, 04 Sep 2014 09:14:51 GMT

To reduce the database hits we are using QVDs.

We are generating a QVD name with no empty spaces.

Regards,

Jagan.

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

nikhilgarg — Thu, 04 Sep 2014 09:24:54 GMT

HEy ,

THanx jagan. NOw i understand the whole working of the code. But a last question:

In the following:

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

WHat is the working of : $(ExtractQVD) ??

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

jagan — Thu, 04 Sep 2014 09:31:48 GMT

Hi Nikhil,

It is the file path for example C:\Qlikview\, instead of hard coding we are maintaining this in variable.

Please close this thread if you got the solution by giving Correct and Helpful answers to the helpful posts.

Regards,

jagan.