0 Replies Latest reply: Aug 17, 2011 12:42 AM by vishal_pai RSS

    Beginners: Subroutine to Load data into QVD from XLSX, Append Data into QVD from XLSX, Load data from multiple QVD files

      Hi,

       

      I am new to Qlikview. I wanted to share the below sub routine that I wrote with the community.

       

      Parameters:

      vQVDFileName - Full path to the QVD file from which you have to load / store data.

      vTableName - Table name into which you load the data.

      vXLSFileName - Full path to the excel file from where data will be loaded for the first time.

      vXLSAppendFileName - Full path to the excel file from where incremental data will be loaded. i.e. Every week data needs to be appended to the DB.

       

      Usage:

      Call DataLoad('D:\Data\Revenue.qvd','Revenue','D:\Data\Revenue.xls','D:\Data\Append_Revenue.xls')

       

      Note:

      The if else structure in the sub routine was based on my specific needs / my ignorance. But I believe that the overall technique is right.

       

      Source code (Feel free to use):

       

      SUB DataLoad (vQVDFileName, vTableName, vXLSFileName, vXLSAppendFileName)

       

      if isnull(FileTime(vQVDFileName)) Then

       

                $(vTableName):

                LOAD *

                FROM

                $(vXLSFileName)

                (ooxml, embedded labels);

       

                Store $(vTableName) into $(vQVDFileName);

       

      ELSEIF (not IsNull(FileTime(vXLSAppendFileName))) Then

       

                $(vTableName):

                LOAD *

                FROM

                $(vQVDFileName) (QVD);

       

                $(vTableName):

                LOAD *

                FROM

                $(vXLSAppendFileName)

                (ooxml, embedded labels);

       

                Store $(vTableName) into $(vQVDFileName);

       

      ELSE

       

                $(vTableName):

                LOAD *

                FROM

                $(vQVDFileName) (QVD);

       

      ENDIF

       

      END SUB