4 Replies Latest reply: Apr 18, 2017 6:27 AM by Ben Williams RSS

    Help me duplicate data on reload

    Ben Williams

      Hi guys,

       

      Hopefully this is easy question to solve - I'm periodically load a bank update file with two tables into QS. The Account Summary: table just holds  [account name] and [balance] fields as at the last download date. (The other table is transactional data which I'm fine with).

       

      When I reload the file I simply want to add to rather than replace the  [account name] and [balance] data so I can visualise account balance over time. 

      I also need to create a automated date field in the load script as the download date is not provided in the raw data.

       

      I tried to do some stuff with incremental loads but it all got a bit confusing - surely this cant be that difficult!

       

      Sorry I'm a newbie, can someone pls help me with a simple solution!

       

      Thanks!

        • Re: Help me duplicate data on reload
          omar bensalem

          load it the first time and store it in a qvd:

           

          account:

          load *,today() as date from SourceAccount;

           

          store account into [Path\account.qvd](qvd);

           

          Once you've done this one time. Now change your script as follow:

           

          account:

          load * from [Path\account.qvd](qvd);

           

          concatenate

          load * , today() as Date)from SourceAccount;


          store account into [Path\account.qvd](qvd);


           

          Suppose We've created this qvd today; and then changed the script as mentionned above and executed it tomorrow.

          What would happen is:

           

          The script will :

           

          import your QVD file containing today's date and the information of today [account name] and [balance].

          account:

          load * from [Path\account.qvd](qvd);

          Then

           

          it will import the information of tomorrow that exists in your data source and concatenate it to the table and information already existing in your qvd

          concatenate

          load * , today() as Date)from SourceAccount;


          Then, it will create a new qvd of this concatenated table , today and tomorrow's informations and store it in the same location.

          store account into [Path\account.qvd](qvd);



          With that, everytime we execute the script we'll have all the history of our information in our QVD with a date field we created.


          Hope this was clear enough

            • Re: Help me duplicate data on reload
              Ben Williams

              Thanks heaps Omar. That did the trick!

               

              Here's the final code I came up with (First block commented out after initial load).  Much appreciated!

               

              /*[Summary$]:

              LOAD [Accounts Summary]as [Account Name],

                [F2]as [Account Balance],

                  Date(today()) as [Balance Date]

              FROM [lib://Desktop/report.xls]

              (biff, embedded labels, header is 4 lines, table is Summary$);

               

              Store [Summary$] into  [lib://Desktop/Summary.qvd'](qvd);*/

               

              [Summary$]:

              LOAD *

              FROM  [lib://Desktop/Summary.qvd'](qvd);

               

              Concatenate

              LOAD

              [Accounts Summary]as [Account Name],

                [F2]as [Account Balance],

                  Date(today()) as [Balance Date]

              FROM [lib://Desktop/report.xls]

              (biff, embedded labels, header is 4 lines, table is Summary$);

               

              Store [Summary$] into  [lib://Desktop/Summary.qvd'](qvd);