12 Replies Latest reply: Jul 8, 2015 10:58 AM by uma nukala RSS

    SQL script to generate QVD?

    Ganesh Reddy

      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?
          Krishna Nagulapally

          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?
              Ganesh Reddy

              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?
                  Krishna Nagulapally

                  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?
                      Krishna Nagulapally

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

                       

                      Thanks

                        • Re: SQL script to generate QVD?
                          Ganesh Reddy

                          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?
                              Krishna Nagulapally

                              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?
                      Jonas Melo

                      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?

                        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?

                          Can anybody pls help me  with this