5 Replies Latest reply: May 17, 2011 9:11 AM by asnajim2000 RSS

    Loading a balance table missing daily records

      Hello

       

      I am trying to upload a transactional table with the following simple structure  (Date  , Balance) ....

       

      In case there is no balance movement during a day, then no record do exist, for exmaple

       

      On 1/4/2011 the balance is 10,000 US$ , therefore will be a record on the table as (1/4/2011 - 10,000$)

       

      On 2/4/2011, a transaction was done by 300. then there will be a record on the table as (2/4/2011 - 9700$)

       

      On 28/4/2011, a transactionw as done of 2000$, then a record will be on the table as (28/4/2011  - 7700$)

       

      so by end of this example, the table will have only 3 records,

       

      My question

       

      How can I build an application that show the balance of each day even for the days not found in the table

      also, which commands to use if I like to see the differences bewteen each day and its previous day with reporting as a movement chart

       

      regards,

        • Re: Loading a balance table missing daily records
          Erich Shiino

          Hi,

          One approach is to create everything on the script. This will reduce the complexity on the interface.

          I create a small example for this.

           

          Basically I run throught the table to get min and max dates, then I created all the dates between min and max.

          After this I read the table again filling the gaps with the previous known value. At the same time, I created another field with the differences between the current value and the last known one.

           

           

          Check app attached.

          Hope it helps,

           

          Erich

           

          tBalance:
          LOAD * INLINE [
              Date, Balance
              01/04/2011, 1000
              02/04/2011, 9700
              28/04/2011, 2000
          ];
          
          load min(Date) as minDate resident tBalance;
          varMinDate = peek('minDate');
          load max(Date) as maxDate resident tBalance;
          varMaxDate = peek('maxDate');
          
          refDate:
          load date($(varMinDate)+rowno()-1) as Date
          
          Autogenerate(num($(varMaxDate)-$(varMinDate))+1) ; 
          
          left join(refDate)
          Load Date, Balance resident tBalance
          ;
          ; 
          
          drop table tBalance; 
          
          //NoConcatenate
          load Date, if(IsNull(Balance),peek('NewBalance'), Balance) as NewBalance,
           if(IsNull(Balance), peek('NewBalance'), Balance)-peek('NewBalance') as var
          resident refDate
          order by Date;
          
          drop table refDate;
          
            • Loading a balance table missing daily records

              Many thanks Erich for the quick response

               

              But the number of accounts I have is 1 million account, and maintaining 5 years data which will lead to have a table of 1 million * 5 * 365 ....

               

              I tried to use your script within my code, but as I am fresh in QlickView, I was not able to manage it ... any help

               

              my existing code is as the following to load the balance table

               

              load Id,

              account_no,

              code,

              currency,

              balance_date,

              balance;

              SQL SELECT *

              FROM  bi.dbo."acounts_balances;

            • Loading a balance table missing daily records
              Goran Korsgren

              If your goal is just to show a chart like this:

               

              continuosgraph.JPG

              you don't need to generate records for every day,

              instead you just use a line chart with the following special settings:

              set the "Display Option" to "Plateau, leading"

              lineplateu.JPG

               

              and set the dimension axis to "Continuos":

              continuos.JPG

               

               

              My load script for this example just looks like this:

               

              Transactions:

              LOAD * INLINE [

                  Date, Transaction

                  1/4/2011, 10000

                  2/4/2011, -300

                  10/4/2011, 900

                  28/4/2011, -2000

              ];

                • Re: Loading a balance table missing daily records
                  Goran Korsgren

                  One more important thing I forgot to mention,

                  you need to set "Full accumulation" for the sum(transaction) expression:

                   

                  fullaccumulation.JPG

                    • Loading a balance table missing daily records

                      Thanks Erish for the help...

                       

                      The objective is to calculate the account holder behavior based on the difference and how long he keeps his holdings

                       

                      with your script I tried to apply it but it didn't work as I am confused of how using the SQL load and the resident fields, so I am attaching here my script hope this will clear for me how to load the table again with the leftjoin statement /////

                       

                      tBalance:
                      Load
                      account_id     as    account_Key ,
                      currency      as    Position_Currency ,
                      position_date           as    Position_Date ,
                      Bal        as    Position_Bal;
                      SQL SELECT *
                      FROM bi.dbo."accounts_table";

                      load min(Position_Date) as minDate resident tBalance;
                      varMinDate = peek('minDate');
                      load max(Position_Date) as maxDate resident tBalance;
                      varMaxDate = peek('maxDate');

                      refDate:
                      load date($(varMinDate)+rowno()-1) as Date
                      Autogenerate(num($(varMaxDate)-$(varMinDate))+1) ;
                      left join(refDate)

                      //  Load Date, Position_Bal resident tBalance (not able to understand how this will work with the SQL select)
                      ;
                      ;

                      NoConcatenate
                      load Date, if(IsNull(Position_HoldingQty),peek('NewBalance'), Position_HoldingQty) as NewBalance,  if(IsNull(Position_HoldingQty), peek('NewBalance'), Position_HoldingQty)-peek('NewBalance') as var resident refDate order by Date;

                      drop table refDate;