3 Replies Latest reply: May 14, 2014 9:25 AM by Martin Pedersen RSS

    QVD file question.

    David Young

      I have a qvd file for transactions.  It consists of transactions from 10 products. Within each product there are three to four different types of transactions.  Each transaction has a different transaction type ID to identify It.  For instance issue tranactions has a transaction type id = 1.  Now with that said my transaction qvd file is over 500 million rows.  I can load the tranasaction qvd with a where clause with a transaction type id = 1 to return the issue transanctions.  The problem is it when the script runs it has to go line by line finding the match criteria.  This is a time consuming progress.  Is there any better way to setup the qvd so it can be read quicker.  Any thoughts? 

        • Re: QVD file question.
          Martin Pedersen

          Hi David,

           

          To get a fast reload of a QVD-file it need to be "QVD-Optimized". In general that means you can not do any calculations in the load and all fields previous loaded into the current table must be included in the QVD-file.

           

          But qlikview allows the use of single parameter Exists() in QVD-optimized reloads.

          
          //Create Data Table:
          Data:
          Load RecNo() as TransId, Floor(Rand()*10) as TypeId autogenerate 5000000;
          Store Data into data.qvd;
          Drop Table Data;
          
          
          //Define Type 2 Load
          _Type:
          Load 8 as TypeId autogenerate 1;
          
          
          //Load Table from
          TransTable:
          Load * From data.qvd (qvd) Where Exists (TypeId);
          
          
          // Or Load TransId, TypeId From data.qvd (qvd) Where Exists (TypeId);
          
          
          Drop Table _Type;
          
          
          
          
          
          
          
          
          

           

          The above show the creation of a Data-Table with 5.000.000 records grouped in 10 random Types (1-10).

           

          Then I specify what type i want to load in a temp-table => In this case TypeId = 8.

           

          Finally I Load the Data-table as QVD-Optimized only for Type 8 with the use of "Exists(TypeId)".

           

          Read more about QVD-Optimized loads here:

          Some examples of things that will cause a non-optimised load are:

          - Adding new fields to the table
          - Deriving new values from a field in the QVD
          - Retrieving a field twice
          - Most WHERE conditions
          - Joining to an existing in memory table
          - Loading data into a mapping table

          In contrast the things you are allowed to do are:

          - Rename fields
          - Omit fields
          - Do a simple one field WHERE EXISTS on a field returned in the record set

          http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

          • Re: QVD file question.
            christian juillard

            Hi David,

             

            if i understand well you have a big QVD holding everything and you want to load only Type =1

             

            Solution 1 :

            Load everything and put a list box to select value 1.

             

            Solution 2 :

            when you store qvd make it by type, so store one Type_1.qvd, Type_2.qvd, ...  you can choose then which you xant to load

             

            No more ideas

            Chris

            • Re: QVD file question.
              Antoine Frangieh

              Try an optimsied load, assuming that your source is a QVD.

               

              Temp:

              LOAD * Inline [

              type id,

              1

              ]

              ;

               

              Source:

              LOAD

                   Field1,

                   [type id]

              FROM yoursource.qvd

              WHERE Exists ([type id]);

               

              Drop table Temp;