Qlik Community

QlikView Documents

Documents for QlikView related information.

Generic Script for QVD Generator

Not applicable

Generic Script for QVD Generator

Hi All, In the Qlikview Development, QVD creation is the very common and we create the QVD Generators qvw files for this tasks.

So, In this document I will give briefly about the Generic script for all QVD Generators. This script used across the multiple QVD generators files with other DB connection and List of tables needs to be in QVD's .

Please find the below script:

//DB Conncetion

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

// #### DB COnnection #### This will be change if we have more DB's

ODBC CONNECT ... ;

// ##### QVD Path ####### This will be change if we want to change the QVD folder path

SET vQVDPath = 'qvdata' ;

// List of Tables, Here I am creating with InLine. You can Use txt file or excel file. This will be change

LIST_OF_TABLES:

LOAD * INLINE [

TABLENAME

SCHEMA.TABLE1

SCHEMA.TABLE2

] ;

// From This line, the script is static

// Generate the Loop for each value in the LIST_OF_TABLES table

FOR i=1 to FieldValueCount('TABLENAME')

LET vTableName = FieldValue('TABLENAME',$(i)) ;

$(vTableName):

SELECT * FROM $(vTableName) ;

STORE $(vTableName) into [$(vQVDPath)\$(vTableName).qvd] (qvd);

//QVD DETAILS info

QVDDETAILS:

LOAD '$(vTableName).qvd' AS QVD_NAME ,

       '$(vTableName)' AS SOURCE_TABLENAME,

       NoOfRows('$(vTableName)') AS NO_OF_ROWS

AutoGenerate 1 ;

DROP Table $(vTableName) ;

NEXT i

In the above script, just change the DB connection , QVD Path and List of Tables for the another QVD Generator qvw file.

Tags (2)
Comments
vikasmahajan
Honored Contributor III

Nice Article

Employee
Employee

How can I change when I want to add the excel & csv data source into this generator ?

kmarsden
New Contributor III

This is a great script! Thanks

carbal1952
Contributor II

At last, something interesting !
Thk

angeloguidi
New Contributor II

Thanks for this helpful post!

Bye Angelo

OmarBenSalem
Esteemed Contributor

first of all, i shall thank you for this great topic. Now, I have a question, hope you'll be willing to help:

If I'm not mistaken, this :

"

LIST_OF_TABLES:

LOAD * INLINE [

TABLENAME

  1. SCHEMA.TABLE1
  2. SCHEMA.TABLE2

] ;"

is the list of the tables existing in the db.

but, in my case, I have to do some changes in the tables, I won't import them as they are.

example: I have a table "test", this what I do with it ;

test:

load *

select

detail, confsite,

convert(varchar(10),header,103) as bla bla,

....

from header,confsite,detail...

where bla bla;

so as you can see, there are some further manipulations I should be doing.

What should I do to overcome this and be able to use this great qvd generator script of yours?

Thank you very much !

OmarBenSalem
Esteemed Contributor

when I use tables I've created from the tables I have in the db;

how should I change this part?

LIST_OF_TABLES:

LOAD * INLINE [

TABLENAME

  1. SCHEMA.TABLE1
  2. SCHEMA.TABLE2

] ;"

because when I did like this:

LIST_OF_TABLES:

LOAD * INLINE [

TABLENAME

TABLE1

TABLE2

] ;"

An error was generated. thanks

Not applicable

Hi this is purely loading from Data base. I will write another post how to load the data from excel or txt or csv files.

Not applicable

Hi Omar, are you using SQL Server as your database ?

Are you troubling with LIST_OF_TABLES Inline table ?

Can you please post your error ?

Nice Application.

Version history
Revision #:
1 of 1
Last update:
‎01-07-2014 11:05 PM
Updated by: