7 Replies Latest reply: Jan 4, 2015 2:25 PM by Anand Chouhan RSS

    Incremental load

    Андрей Шепель

      Good day!

      In attached file i have some test model.

      When i reload it every day i need to fill such table:

       

      Spoint        Date                Value

      Spoint1      01-01-2014        1                     <--------------------When i done reload 01.01.2015

      Spoint2      01-01-2014        1


      +


      Spoint1      02-01-2014        1                     <--------------------When i done reload 02.01.2015

      Spoint2      02-01-2014        1


      +


      Spoint1      03-01-2014        1                     <--------------------When i done reload 03.01.2015

      Spoint2      03-01-2014        1


      +


      Spoint1      04-01-2014        1                     <--------------------When i done reload today

      Spoint2      04-01-2014        1


      +


      Spoint1      05-01-2014        1                     <--------------------When i done reload tomorrow

      Spoint2      05-01-2014        1


      In other words, i want to add values 1 for each Spoints for dates between firts date of current month and today date (example in xls file)

       

      Many thanks for advices.

        • Re: Partial load
          Colin Albert

          You need to change your script to use Incremental Load.

           

          There is a simple example in this threadIncremental reload and store into QVD file

           

          There are many more articles on the QlikView Community about this, and in the reference manuals.

            • Re: Partial load
              Андрей Шепель

              Thanks for your answer.

              If you have some time, could you help me to solve my task in example?

              Many thanks..

                • Re: Partial load
                  Anand Chouhan

                  Hi,

                   

                  You can use Incremental load concept here for create the rows for the SPoint 1 and 2 and try with this script

                   

                  1. First  load this tables and store the table in QVD

                   

                  ReloadDetails:

                  LOAD

                  ReloadTime() as ReloadTime,

                  'point 1' as Spoint,

                  1 as Value

                  AutoGenerate 1;

                   

                  LOAD

                  ReloadTime() as ReloadTime,

                  'point 2' as Spoint,

                  1 as Value

                  AutoGenerate 1;

                   

                  STORE ReloadDetails into ReloadDetails.qvd;

                   

                  2. Then load  the store qvd table and then concatenate next day


                  ReloadDetails:

                  LOAD

                  Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

                  'point 1' as Spoint,

                  1 as Value

                  AutoGenerate 1;

                   

                  LOAD

                  Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

                  'point 2' as Spoint,

                  1 as Value

                  AutoGenerate 1;

                   

                   

                  Concatenate(ReloadDetails)

                  New:

                  LOAD ReloadTime,

                       Spoint,

                       Value

                  FROM ReloadDetails.qvd (qvd);

                   

                  STORE ReloadDetails into ReloadDetails.qvd;

                   

                   

                  Regards

                  Anand

                    • Re: Incremental load
                      Андрей Шепель

                      Thanks for answer.

                      It's very useful for example, where i have 2 Spoints. In my work model, for example, i will have more than 20 Spoints and i think, it's not very simple for such situation.

                       

                      Thank you!

                        • Re: Incremental load
                          Anand Chouhan

                          Hi,

                           

                          Then try this in the first step

                          1. First create the QVD with find Max point value in the table suppose it is max upto 10 also

                           

                          MaxPoint:

                          LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;

                          LET vMax = Peek('MaxPointNum',0,'MaxPoint');

                           

                          ReloadDetails:

                          LOAD

                          Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

                          'point ' &IterNo() as Spoint,

                          1 as Value

                          AutoGenerate 1 While IterNo() <= $(vMax);

                          STORE ReloadDetails into ReloadDetails.qvd;

                           

                          2. And in this step concatenate the

                          MaxPoint:

                          LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;

                          LET vMax = Peek('MaxPointNum',0,'MaxPoint');

                           

                          ReloadDetails:

                          LOAD

                          Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

                          'point ' &IterNo() as Spoint,

                          1 as Value

                          AutoGenerate 1 While IterNo() <= $(vMax);

                           

                          Concatenate(ReloadDetails)

                          LOAD ReloadTime,

                               Spoint,

                               Value

                          FROM

                          ReloadDetails.qvd

                          (qvd);

                          STORE ReloadDetails into ReloadDetails.qvd;

                           

                           

                          Regards

                          Anand

                            • Re: Incremental load
                              Андрей Шепель

                              Thanks for answer.

                              I found other solution to rewrite records by last actual date.

                               

                              SecondLoad:

                              LOAD Spoint as Data1,

                              Date,

                              Value

                                      

                              FROM

                              [D:\QVDevelopment\ADMINs\ANDREY SH\Test\Data.xlsx]

                              (ooxml, embedded labels, table is Sheet1)

                               

                              WHERE (Date <= $(lastReloadTime));

                               

                              But after reload it show me error:

                              Error in expression: ')' expected

                               

                              Maybe you have some ideas to solve it ?