Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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 ...
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.
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.
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.
For step 5: we are not dropping QVD, we are just dropping table in qlikview file, since we are not using it.
Regards,
Jagan.
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
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.
Hi Nikhi,
That statement replaces white spaces with empty values, simply removes whitespaces.
Regards,
jagan.