topic Dynamic Procedure To Create Qvd in QlikView

Dynamic Procedure To Create Qvd

Tue, 11 Feb 2014 06:05:10 GMT

Emp:
LOAD * INLINE [
    EmpName, Salary
    Ram, 4587
    Shyam, 1500
    John, 5000
    James, 2500
    Jim, 1000
];

Emp2:
LOAD * INLINE [
    EmpId, Office
    1, UAE
    2, Ind
    3, Uk
    5, Us
    4, Pune
];

Emp3:
LOAD * INLINE [
    EmpId, Office
    1, UAE
    2, Ind
    3, Uk
    5, Us
    4, Pune
];

Using above three tables create the dynamic procedure to create qvd using single store functionality and also it deletes the tables,
what i want i want Create the qvd and delete the tables with single statement and need to use that statement when ever we required to delete or create qvd)

Re: Dynamic Procedure To Create Qvd

jaimeaguilar — Tue, 11 Feb 2014 06:30:16 GMT

Hi,

try with this:

Set vQVD_Folder = 'some_folder' //put here the directory where you want to store your qvds

//Store QVDs

FOR i = 0 TO NoOfTables() - 1

LET Table = TableName(i);

STORE [$(Table)] INTO [$(vQVD_Folder)\$(Table).qvd];

NEXT

//Drop tables

LET j = NoOfTables();

DO WHILE j > 1

LET TableDrop = TableName(0);

DROP TABLE [$(TableDrop)];

LET j = NoOfTables();

LOOP

regards

Re: Dynamic Procedure To Create Qvd

jjordaan — Tue, 11 Feb 2014 06:41:52 GMT

See the below example.

It's a simple one but I hope it helps.

Tables2BeLoaded:

LOAD

Replace(Replace([SQL Table Name],'.','_'),'/','_') AS SQLTable

,Alias

,Active

Resident Tables

Where Active = 1;

DROP Table Tables;

LET vRowTables = NoOfRows('Tables2BeLoaded'); // Get the total number of tables:

FOR i = 1 to $(vRowTables) // Loop through every row:

LET vSQLTable = Peek('SQLTable', $(i)-1, 'Tables2BeLoaded');

LET vTable = Peek('Alias', $(i)-1, 'Tables2BeLoaded');

TRACE i: $(i);

[$(vTable)]:

SQL SELECT *

FROM "$(vDataSource)".dbo."$(vDB_Company)$$(vSQLTable)";

STORE $(vTable) into [$(vDir)$(vDataSource)$$(vDB_Company)$$(vTable).qvd] (qvd);

DROP Table $(vTable);

NEXT i; //Loop on tables:

Re: Dynamic Procedure To Create Qvd

Tue, 11 Feb 2014 06:55:11 GMT

Thanks Jaime..its work..

Re: Dynamic Procedure To Create Qvd

Tue, 11 Feb 2014 06:56:22 GMT

Thanks jeroen For Reply both are working..

Re: Dynamic Procedure To Create Qvd

jjordaan — Tue, 11 Feb 2014 06:59:23 GMT

Hi Dhananjay,

Can you mark Jaime and my post as a correct or helpful answer so other community users can also work with these answers.

Thank you