topic Re: SQL script to generate QVD? in QlikView

SQL script to generate QVD?

ganeshreddy — Wed, 01 Jul 2015 17:19:33 GMT

Hi All,

How to generate a qvd to get fields with following SQL Script, please give me QlikView table structure script for this.

;WITH LOB_CTE(LOB_CODE,LOB_DESCRIPTION,PARENT,TOPPARENT,DEPTH) AS

(

SELECT A.LOB_CODE,

A.LOB_DESCRIPTION,

A.PARENT,

A.LOB_CODE AS TOPPARENT,

0 AS DEPTH

FROM Dim_LOB A WHERE Parent='20BSU0000'

UNION ALL

SELECT C.LOB_CODE,

C.LOB_DESCRIPTION,

C.PARENT,

LOB_CTE.TOPPARENT,

LOB_CTE.DEPTH+1 AS DEPTH

FROM Dim_LOB C

INNER JOIN LOB_CTE

ON LOB_CTE.LOB_CODE = C.Parent

)

Cheers,

Ganesh.

Re: SQL script to generate QVD?

Qrishna — Wed, 01 Jul 2015 17:25:34 GMT

LET vQVDStorePath = 'your qvd store path\'

TableName_Temp:

Your SQL Script which generates Field Names.;

TableName:

Load *

Resident TableName_Temp;

Drop Table TableName_Temp;

STORE TableName INTO $(vQVDStorePath)TableName.qvd;

DROP TABLE TableName;

Re: SQL script to generate QVD?

ganeshreddy — Wed, 01 Jul 2015 17:37:45 GMT

Hi chitanya,

Thanks for your replay, I got that structure which you have provided. please tell me the script which you have mentioned above, that will be helpful for me.

Cheers,

Ganesh.

Re: SQL script to generate QVD?

Qrishna — Wed, 01 Jul 2015 17:45:20 GMT

Were you able to generate qvd?

Script explanation:

LET vQVDStorePath = 'your qvd store path\'

- storing the path in a variable vQVDStorePath, where this variable can be used in multiple locations your script if you want to generate lots of qvds.

TableName_Temp:

Your SQL Script which generates Field Names.;

- This piece of script does all the SQl transformations based on your sql code and generates the fields and their data

STORE TableName INTO $(vQVDStorePath)TableName.qvd;

DROP TABLE TableName;

- This piece of code generates the qvd format files and drops the table and its contents from qlikview memory.

Cheers

Re: SQL script to generate QVD?

Qrishna — Wed, 01 Jul 2015 18:01:50 GMT

Hi please mark it answered if you were able to generate the qvd.

Thanks

Re: SQL script to generate QVD?

ganeshreddy — Wed, 01 Jul 2015 18:06:23 GMT

Sorry Brother you are getting my question wrong, I am asking the highlighted portion on bellow Script. Please take Sql script which i have provided as reference.

LET vQVDStorePath = 'your qvd store path\'

TableName_Temp:

Your SQL Script which generates Field Names.;

TableName:

Load *

Resident TableName_Temp;

Drop Table TableName_Temp;

STORE TableName INTO $(vQVDStorePath)TableName.qvd;

DROP TABLE TableName;

Re: SQL script to generate QVD?

jonas_rezende — Wed, 01 Jul 2015 18:36:56 GMT

Hi.

See the code below.

SET vPath = '..\example\dw\qvd\'; //variable path

SET vNameTable = 'TABLE_DIM_LOB'; //variable name table

$(vNameTable): //Structured in language native of Qlikview

LOAD

LOB_CODE,

LOB_DESCRIPTION,

PARENT,

TOPPARENT,

DEPTH;

SQL //Command SQL work together with database driver

WITH LOB_CTE(LOB_CODE,LOB_DESCRIPTION,PARENT,TOPPARENT,DEPTH) AS

(

SELECT A.LOB_CODE,

A.LOB_DESCRIPTION,

A.PARENT,

A.LOB_CODE AS TOPPARENT,

0 AS DEPTH

FROM Dim_LOB A WHERE Parent='20BSU0000'

UNION ALL

SELECT C.LOB_CODE,

C.LOB_DESCRIPTION,

C.PARENT,

LOB_CTE.TOPPARENT,

LOB_CTE.DEPTH+1 AS DEPTH

FROM Dim_LOB C

INNER JOIN LOB_CTE

ON LOB_CTE.LOB_CODE = C.Parent

);

STORE [$(vNameTable)] INTO [$(vPath)$(vNameTable).qvd](qvd); //Storing in format qvd

DROP Table $(vNameTable); //Destroy memory table;

Hope this helps.

Re: SQL script to generate QVD?

Qrishna — Wed, 01 Jul 2015 18:41:12 GMT

LET vQVDStorePath = 'your qvd store path\'

TableName:

LOAD LOB_CODE,

LOB_DESCRIPTION,

PARENT,

TOPPARENT,

DEPTH;

SQL

WITH LOB_CTE(LOB_CODE,LOB_DESCRIPTION,PARENT,TOPPARENT,DEPTH) AS

(

SELECT A.LOB_CODE,

A.LOB_DESCRIPTION,

A.PARENT,

A.LOB_CODE AS TOPPARENT,

0 AS DEPTH

FROM Dim_LOB A WHERE Parent='20BSU0000'

UNION ALL

SELECT C.LOB_CODE,

C.LOB_DESCRIPTION,

C.PARENT,

LOB_CTE.TOPPARENT,

LOB_CTE.DEPTH+1 AS DEPTH

FROM Dim_LOB C

INNER JOIN LOB_CTE

ON LOB_CTE.LOB_CODE = C.Parent

);

;

STORE TableName INTO $(vQVDStorePath)TableName.qvd;

DROP TABLE TableName;

Re: SQL script to generate QVD?

ganeshreddy — Thu, 02 Jul 2015 07:40:00 GMT

hi Melo,

It shows error as below, please help

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near ')'.

SQL

WITH LOB_CTE(LOB_CODE,LOB_DESCRIPTION,PARENT,TOPPARENT,DEPTH) AS

(

SELECT A.LOB_CODE,

A.LOB_DESCRIPTION,

A.PARENT,

A.LOB_CODE AS TOPPARENT,

0 AS DEPTH

FROM Dim_LOB A WHERE Parent='20BSU0000'

UNION ALL

SELECT C.LOB_CODE,

C.LOB_DESCRIPTION,

C.PARENT,

LOB_CTE.TOPPARENT,

LOB_CTE.DEPTH+1 AS DEPTH

FROM Dim_LOB C

INNER JOIN LOB_CTE

ON LOB_CTE.LOB_CODE = C.Parent

)

Re: SQL script to generate QVD?

jonas_rezende — Thu, 02 Jul 2015 12:07:38 GMT

Hi.

This error is in your sql query. See if the link below help.

http://stackoverflow.com/questions/4696041/using-the-with-clause-sql-server-2008

Hope this helps.

Re: SQL script to generate QVD?

Qrishna — Thu, 02 Jul 2015 14:23:56 GMT

Make sure you end the statement with a semi colon ';' .

Re: SQL script to generate QVD?

Tue, 07 Jul 2015 17:57:33 GMT

Hi Krishna Chaitanya,

can you pls give me complete script for multiple qvd generators:

I have to create many:

for loop script with sql db table names like:

sql select * from "SF_Con".dbo."EMP_COMP";

sql select * from "PD_DATA".dbo."ORG_NM";

sql select * from "AFS_VAL".dbo."DBNM_SF";

into qvd's with single store command

Thanks In Adavnce

Re: SQL script to generate QVD?

Wed, 08 Jul 2015 14:58:10 GMT

Can anybody pls help me with this