2 Replies Latest reply: Mar 2, 2013 1:02 AM by David Zenesh RSS

    Dealing with a table

      Hi all,  

      I'll appreciate your promptly help on a issue I have:

      I have Customer table with Date and Amounts  which looks like this:

       

       

      Date                      Account               Amount

      31/01/2012         101                         1,000

      31/01/2012         103                         1,500

      28/02/2012         103                         900

      31/03/2012         101                         500

      30/04/2012         103                         300

      30/04/2012         101                         0

          

      See that for account '101' I have no Amount for the date 28/2/2012, and
      for account '103' I don't have amount for 31/03/2012.

       

      What I would like to do is to write a script that aggregates the account for each month.

      In case that there is no amount for
      account on a certain month than we should take the amount from the previous
      month.

      means that the table should look like this:

      31/01/2012         101                         1,000

      31/01/2012         103                         1,500

      28/02/2012         101                         1,000     added

      28/02/2012         103                         900

      31/03/2012         101                         500

      31/03/2012         103                         900         added

      30/04/2012         103                         300

      30/04/2012         101                         0

       

      Than to do the aggregation for each month..

       

      01/2012      2,500

      02/2012      1,900

      03/2012      1,400

      04/2012      300

       

      I'll appreciate you'r help on this.

       

      Best

      David

        • Re: Dealing with a table
          Phaneendra Kunche

          May be this?

           

           

           

          Data:

          LOAD * INLINE [  

              Date, Account   ,  Amount   

              01/2012,101,       1000   

             01/2012,103,       1500   

             02/2012,103,       900   

              03/2012,101,       500   

              04/2012,103,       300   

              04/2012,101,       0

          ];

           

          //Add all months to the Data..

          Month_Account:

          Load distinct Account Resident Data;

          left join

          Load * Inline [

          Date

          01/2012

          02/2012

          03/2012

          04/2012

          ];

           

          Month_Account_NEW:

          NoConcatenate

          Load * Resident Month_Account;

          left join

          Load * Resident Data ;

           

          Drop Tables Month_Account,Data;

           

          Final:

          NoConcatenate

          Load Date,Account,

              IF (Account=PEEK('Account') AND isnull(Amount),PEEK('NEW_VAL'),Amount) AS NEW_VAL

           

          Resident Month_Account_NEW order by Account Desc;

           

          Drop Table Month_Account_NEW;