SQL script to generate QVD?

[ganeshreddy]

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.

[krishna_2644]

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;

[ganeshreddy]

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.

[krishna_2644]

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

[krishna_2644]

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

Thanks

[ganeshreddy]

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;

[jonas_rezende]

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.

[krishna_2644]

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;

[ganeshreddy]

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

  )

[jonas_rezende]

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.