29 Replies Latest reply: Jan 21, 2015 7:16 AM by neetha P RSS

    QVD Creation

    sanjayrajsharmas

      Hi All,

       

       

      How to create QVD files, Different ways to create a QVD

       

       

      SRS

        • QVD Creation
          Christophe Brault

          Hi,

           

          After your load statement, you can simply write this code to store a table in a QVD :

           

          STORE YourTable into YourQVDName.QVD;

          • QVD Creation
            janardhan

            Hi Sanjay ,

             

            Nilesh is correct but he missed one thing

             

             

            tablename1:

             

            load * from

             

            excel1;

             

             

             

            store tablename1 into tablename1.qvd;

             

             

             

            tablename2:

             

            load *

             

            from excel2;

             

             

             

            store tablename2 into tablename2.qvd;

            • Re: QVD Creation
              Anand Chouhan

              Hi,

               

              Use some thing like this in the script

               

              Table1:

               

              load * from

              D1;

               

              store Table1 into Table1.qvd;

               

              Table2:

               

              NoConcatenate

               

              load * from

              D1;

               

              store Table2 into Table2.qvd;

               

               

              Regards,

              Anand

              • Re: QVD Creation
                Sokkorn Cheav

                Hi,

                 

                I try your script and working for me. Let see in the attached files.

                 

                Edit: Put all files in the same folder.

                 

                Regards,

                Sokkorn

                • Re: QVD Creation
                  Anand Chouhan

                  Hi,

                   

                   

                  If you not need the loaded tables so delete it and use the QVD files.

                   

                   

                   

                  Regards,

                  Anand

                    • QVD Creation
                      Ruby Otero

                      may i ask... is there an effect of you issue drop table after the store statement?

                       

                      and also what is nonconcaatenate use for?

                        • Re: QVD Creation
                          Anand Chouhan

                          Hi,

                           

                          No there is not any effect if you put drop statement after the store statement and noconcatenate command is used to not concatenate to tables like if there is any tables name is same and fileds name is also same so in qlikview is automatically concatenate to the previous table noconcatenate is forcelly used to not concatenate to the table previous table.

                           

                          Regards,

                          Anand

                      • Re: QVD Creation
                        Nelson Adao

                        Hi all!

                        This is the error that is not allowing me to store the tables into QVDs:

                        Cannot open file 'C:\Users\X\Desktop\Qlikview Test \Test.dbo."vw_Cha"' The system cannot find the file specified.

                        Cha:
                        load * from
                        Test.dbo."vw_Cha"

                         

                        Anyone can help ?

                         

                        Thanks in advance for your help!

                        N.

                        • Re: QVD Creation
                          Raj Kumar

                          Hi Nelson

                           

                           

                           

                           

                          THIS  STORES ALL THE TABLES LOADED INTO QVDS

                           

                          //Loop through all of the table names and store them to QVD.

                           

                          LET vTableVol = NoOfTables();//Get the number of tables loaded

                           

                          FOR i = 0 TO $(vTableVol) - 1 ;

                           

                          LET vTableNameS = TableName($(i));


                          IF '$(vTableNameS)' <> 'Vars' AND '$(vTableNameS)' <> 'Tables' AND '$(vTableNameS)' <> 'DBInfo' THEN //Do not load the system tables in the main QVD folder

                           

                          STORE $(vTableNameS) INTO  $(vQVDFilePath)$(vTableNameS).qvd (qvd);

                           

                          ELSE

                           

                          STORE $(vTableNameS) INTO  $(vQVExSysPath)$(vTableNameS).qvd (qvd);

                            • Re: QVD Creation
                              Nelson Adao

                              Hi Raj!! Thanks a lot for your quick reply!

                              I have reloaded my script with this piece of code and it stored the tables as variables but did not create a separate .QVD file. Would you be so kind to explain me how you could do that ?

                              Thanks.

                            • Re: QVD Creation
                              neetha P

                              Hi,

                               

                              Check below:

                               

                              TempTable:
                              LOAD Distinct Year([Accounting Period]) AS YearField
                              FROM QVD\Main.qvd(qvd)
                              Order by Year([Accounting Period]) asc;

                              NoConcatenate

                              Table:
                              Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
                              Resident TempTable;

                              Let vMin=Peek('minYear', 0, 'Table');
                              Let vMax=Peek('maxYear', 0, 'Table');

                              FOR i=$(vMin) to $(vMax)
                              LET vYear = Peek(' YearField ',$(i),'TempTable');

                              ConsolidateYearWise:
                              Concatenate
                              LOAD * ,
                              Year([Accounting Period]) AS YearFlag
                              FROM QVD\Main.qvd(qvd)
                              Where Year([Accounting Period]) = '$(vYear)';

                              Next

                              STORE ConsolidateYearWise into ConsolidateData.qvd;

                              DROP Table ConsolidateYearWise;

                              DROP Table TempTable;
                              DROP Table Table;


                              //Yearly QVD:

                              TempTable:
                              LOAD Distinct Year([Accounting Period]) AS YearField
                              FROM QVD\Main.qvd(qvd)
                              Order by Year([Accounting Period]) asc;

                              NoConcatenate

                              Table:
                              Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
                              Resident TempTable;

                              Let vMin=Peek('minYear', 0, 'Table');
                              Let vMax=Peek('maxYear', 0, 'Table');

                              FOR i=$(vMin) to $(vMax)
                              LET vYear = Peek(' YearField ',$(i),'TempTable');

                              SeparateYearWise:
                              NoConcatenate
                              LOAD * FROM QVD\Main.qvd(qvd)
                              Where Year([Accounting Period]) = '$(vYear)';

                              STORE SeparateYearWise into TableName_$(vYear).qvd;
                              DROP Table SeparateYearWise;

                              Next

                              DROP Table TempTable;
                              DROP Table Table;


                              //Past 6 months QVD:

                              LET vAccountingPeriod = num(addmonths(today(),-6));

                               

                              SixMonthsData:
                              LOAD * FROM QVD\Main.qvd(qvd)
                              Where [Accounting Period]>=$(vAccountingPeriod);

                              STORE SixMonthsData into TableName_SixMonthsData.qvd;
                              DROP Table SixMonthsData;

                              • Re: QVD Creation
                                venkatasreekanth2020

                                Hi,

                                 

                                Use like this in the script

                                 

                                TableName:

                                 

                                load * from

                                D1;

                                 

                                store TableName into TableName.qvd;

                                • Re: QVD Creation
                                  Nelson Adao

                                  Could you also be so kind and help me with the following?

                                  I would like to convert all date fields across my application from DD/MM/YYYY HH:MM:SS to DD/MM/YYYYMy question is, can I make it directly in my initial script rather than in an object expression, so that I will not have to amend it each time I insert a new object?

                                  Thanks for your time!

                                  Kind regards,

                                  Nelson.