4 Replies Latest reply: Apr 11, 2018 1:49 AM by Staffan Johansson RSS

    How to load optimised data

    Rakesh Pareek

      Hi,

       

      I am fetching data from a big table with three where conditions with resident load as Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'.

      This condition is taking too much time in reloading the data.

      KIndly suggest any optimised way, so that I can reduce reload time.

       

      Thanks in advance.

        • Re: How to load optimised data
          Staffan Johansson

          Instead of doing a resident load, do a Preceding Load

           

          LOAD *

          Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'

          ;

          LOAD

          from yoursoucefile;

            • Re: How to load optimised data
              Rakesh Pareek

              Hi Staffan,

               

              I have previously loaded data, from which I want to load some data (Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'), then how it is possible to load with Preceding Load at the place of Resident?

                • Re: How to load optimised data
                  Staffan Johansson

                  Hi Rakesh,

                  This is a preceding load (the Bold part) This load from Your sourcefile and then read it again in memory With Your where statment.

                   

                  Table:

                  LOAD

                  *

                  Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'

                  ;

                  LOAD

                  RBUKRS,

                  Flag_PnL,

                  Flag_BS_PnL,

                  ....

                  ....

                   

                  from yoursoucefile;

                   

                  This is a resident LOAD, which means that you load the main table twice, second time With where statment and drop the first loaded table.

                  Table:

                  LOAD

                  RBUKRS,

                  Flag_PnL,

                  Flag_BS_PnL,

                  ....

                  ....

                  from yoursoucefile;

                   

                  New_Table:

                  LOAD

                  RBUKRS,

                  Flag_PnL,

                  Flag_BS_PnL,

                  ....

                  ....

                  Resident Table

                  Where RBUKRS='4100' and Flag_PnL='Original_PnL' and Flag_BS_PnL='Original'

                  ;

                   

                  Drop table Table;

              • Re: How to load optimised data
                Felip Drechsler

                Hi Rakesh,

                 

                Use the ETL layers to extract the data with your where conditions and store the QVDs.

                The transform and load layers will use the generated QVDs in an optimized way, if you simply load the tables with simply where clauses.

                 

                See the following as example:

                 

                ETL layer in qlikview