QVD Generation

[Vikash2024]

Suppose I have more than 300 data table. for that i want to generate QVD's for each data table at one time.

how to do that in qlik?

[Taoufiq_Zarra · Accepted solution]

@Vikash2024  you can use :

SET QVDPath = 'C:\QVDs\'; // output QVD

FOR EACH TableName IN 'Table1', 'Table2', 'Table3', 'Table4', ... // Your tables

$(TableName):
LOAD *
FROM [DataSource]
WHERE TableName = '$(TableName)';

// create QVD
STORE $(TableName) INTO [$(QVDPath)$(TableName).qvd] (qvd);

// Drop de la table
DROP TABLE $(TableName);

NEXT;

[marksouzacosta]

You can also extend @Taoufiq_Zarra solution replacing the manually entered list of tables with SQLTables statement (SQLTables | Qlik Cloud Help) or doing a query to return the list of tables from your Database. This will give you a sequential ETL process, one table at time.

I personally prefer creating the 300 individual Script Apps in a specific Space and running all of Script Apps at the same time with an Automation. You can achieve that using one single QVS file and using the Script App Name as a variable to identify which table to load. Let me know if you are interested in this approach then I can detail that to you.

Regards,

Mark Costa

[BuildItStrong]

I went through this process a few months ago.  The details of the solution described here are specific to tables stored in SQL Server, but I know that there are parallels for this in Oracle, and I suspect also in other database engines.

The overall process creates a view in the database to present column data, and a script (could be turned into a stored procedure, but I left it as a script) that creates a load script that I could paste into the Qlik data load editor.  I created several analytical dashboard apps for this load.  I put all of the load scripts for a specific database and schema into a single load script, so that I could load all of the "like data" together.  You may have some other approach that you prefer.

Each time before you run the load script, edit the 4 variables in the top of the script.  

@SourceSchema is the schema that contains the table in the database.  

@TableName is the name of the table in the database.  

@FilePrefix is a value that may be appended to the front of a field name.  In the case of some of my databases, many tables had a single key field named:  GUID.  I wanted to prefix a value to the front of the this field name to that the Qlik engine would not automatically join all of the tables on this field name.

@SourceDatabase is the name of the database that contains the table of interest.

I found that once I had this script tuned for my database, it only took a couple of minutes to generate the load script for a table.

Now that Qlik supports an enhanced file/folder structure for data files, you may want to modify this script to support a more sophisticated file structure.

Note that on my on-premises Qlik installation, we also loaded dashboards from stored procedures.  You can't load dashboards with stored procedures in Qlik Cloud, so I create agent jobs to run the store procedures and store the results into tables.  I then loaded the tables to Qlik Cloud with this technique.

We also loaded dashboard from views.  I have a similar script that I developed to load Qlik Cloud QVD files from views.

Having said all of that, Later versions of the data gateway support passing user credentials when loading data, so we no longer use a library of QVD files on Qlik Cloud.  We load dashboards directly from our on-premises SQL Server database.  The new Qlik tools inherited from Talend also have some cool ETL/ELT capabilities that might make your thoughts about creating a library of QVD data files obsolete.

You may also want to see my early (for my implementation of Qlik Cloud) rambling about data governance using QVD files here:  Qlik-Cloud-Data-Integration/Qlik-Cloud-security-model-to-control-access-to-data 

This procedure relies on a view into the database data dictionary that presents the fields of the tables as a table-like view.  Here is the SQL Server specific code:

-- create view dbo.vwGetColumnDataTypes

USE [Database]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE view [dbo].[vwGetColumnDataTypes] as

select top 100 percent * from (

select  

s.name as SchemaName

,f.name as TableName

,c.column_id

,c.name as ColumnName

,stu.name as UserType

, case when stu.name = 'nvarchar' and c.max_length = -1 then 'max' 

   when stu.name = 'nvarchar' then cast((c.max_length)/2 as varchar(5)) 

   else cast(c.max_length as varchar(5)) end  as max_length

, c.precision

, c.scale

, c.is_nullable

, c.is_computed

,case when c.is_nullable = 0 then column_id

  else 1000 end as index_column_id

from sys.objects f 

JOIN sys.schemas s on s.schema_id = f.schema_id

JOIN sys.columns c on f.object_id = c.object_id

inner join  sys.types stu on c.user_type_id = stu.user_type_id

where f.type in ( 'U')

--  and c.is_nullable = 0

--  distinct data types:  varchar, datetime, numeric, float, date, char

) dtl01

order by SchemaName, TableName, column_id

GO

Here is the SQL Server script for creating the Qlik table load script:

-- Go to Tools -> Options -> Query Execution -> SQL Server -> Advanced.  Uncheck the "Show Completion Time" checkbox.  Press OK.  Restart SSMS.

Declare @TableName nvarchar(255) = 'Discipline'

Declare @SourceSchema as nvarchar(255) = 'RepositoryPT'

Declare @FieldPrefix as nvarchar(5) = 'DC_'

Declare @SourceDatabase as nvarchar(255) = 'FinfrockBI'

--select * from vwGetColumnDataTypes where TableName = @TableName order by SchemaName, TableName

declare @strExecSql as nvarchar(max)=''

select @strExecSql=@strExecSql + strSql from (

Select 0 as Index_column_id, -5 as Column_ID, '['+@TableName+']:

' as strSQL

union all

select 0 as index_column_id, -1 as Column_ID, 'LOAD '  as strSql

union all

select

index_column_id,

column_id,  char(10) +char(9) + CASE WHEN column_id = 1 then ' ' else ',' end  + ''+ case when ColumnName='GUID' then @TableName+'GUID' 

                                                                                          when right(ColumnName,4) != 'GUID' then @FieldPrefix+ColumnName 

  else ColumnName end + '' 

from vwGetColumnDataTypes  --select * from vwGetColumnDataTypes order by SchemaName, TableName

where SchemaName = @SourceSchema 

and TableName =  @TableName

and ColumnName != @TableName+'SK'

and ColumnName not in ('RowStartDate','RowEndDate','ChangeDate','CurrentFlag')

union all

Select 0 as index_column_id, 1000 as Column_ID, '

    ,'+@FieldPrefix+'ChangeDate; '  as strSql

union all

Select 0 as index_column_id, 1000 as Column_ID, '

SELECT ' as strSQL

union all

select

index_column_id,

column_id,  char(10) +char(9) + CASE WHEN column_id = 1 then ' ' else ',' end  + ''+ ColumnName + case when ColumnName = 'GUID' then ' as ' + @TableName+ColumnName

   when RIGHT(ColumnName,4) = 'GUID' then ''

                                                                                                       else ' as ' + @FieldPrefix+ColumnName end

from vwGetColumnDataTypes  --select * from vwGetColumnDataTypes order by SchemaName, TableName

where SchemaName = @SourceSchema 

and TableName =  @TableName

and ColumnName != @TableName+'SK'

and ColumnName not in ('RowStartDate','RowEndDate','ChangeDate','CurrentFlag')

union all

Select 0 as index_column_id, 1000 as Column_ID, '

    ,ChangeDate as ' + @FieldPrefix+'ChangeDate ' as strSql

union all

Select 0 as index_column_id, 2000 as Column_ID, '

FROM '+@SourceDatabase+'.'+@SourceSchema+'.'+@TableName+' where CurrentFlag = 1;

'

union all 

Select 0 as index_column_id, 2001 as Column_ID, '

Let MyMessage = ''>>> Store ' + @TableName + ' To QVD'';

'

union all

Select 0 as index_column_id, 2002 as Column_ID, '

TRACE $(MyMessage);

'

union all

Select 0 as index_column_id, 2002 as Column_ID, '

Store * from ' + @TableName + ' into [lib://wh_'+@SourceSchema+':DataFiles/'+@TableName+'.qvd];

drop table ' + @TableName + ';'

) dtl01

print @strExecSql

-- I hope this helps.

[Kushal_Chawda · Accepted solution]

@Vikash2024  I hope you have list of table names stored somewhere so that you can loop over each table.

// list of table names
TableList:
Load TableName
FROM table;

for Each vTableName in FieldValueList('TableName')

[$(vTableName)]:
SQL SELECT *
FROM [database].[$(vTableName)];

Store [$(vTableName)] into lib://FolderConnection/QVD/$(vTableName).qvd(qvd);

Drop Table [$(vTableName)];

Next vTableName

[Vikash2024]

Thanks Taoufiq..

[Vikash2024]

Thanks Kushal...

[Vikash2024]

what if table name come from the database.?

[Kushal_Chawda · Accepted solution]

@Vikash2024  replace TableList load as database table

// Connect to database
LIB CONNECT TO 'sql_source';

// list of table names
TableList:
SQL SELECT TableName
FROM database.table_name;

for Each vTableName in FieldValueList('TableName')

[$(vTableName)]:
SQL SELECT *
FROM [database].[$(vTableName)];

Store [$(vTableName)] into lib://FolderConnection/QVD/$(vTableName).qvd(qvd);

Drop Table [$(vTableName)];

Next vTableName

[Vikash2024]

It's working. thank it was really helpful.