3 Replies Latest reply: Dec 19, 2013 10:55 AM by Paul Edrich RSS

    Calculating total charges from multiple transactions

    Paul Edrich

      Hi There and again thanks for any help.

       

      I would like to have a   'TotalCharges' Calculation in my script with an account closed date when the Status is 10 with any interim payments of status 7 also considered.

       

      So in the example below item 1 would have a 'ClosedDate' of 02/03/15 and a 'TotalCharge'  of 14.00.

       

      TransDate          ItemID          StatusID        AmountPaid

      02/01/15               1                    1                    -15.00   

      03/01/15               2                    1                    -55.00

      02/01/15               3                    1                    -25.00   

      02/03/15               1                    7                       7.00

      02/03/15               4                    1                    -45.00

      02/03/15               1                    10                    22.00

      19/03/15               3                    10                    55.00

       

      StatusID and Item ID are in a StatusTable

      TransDate & AmountPaid are in a TransTable

       

       

      Paul

        • Re: Calculating total charges from multiple transactions

          Put your data into QVD and then :

           

          LOAD

          Transdate AS ClosedDate

          sum(AmountPaid) AS TotalCharge

          From MyTable.qvd (qvd)

          Group By TransDate
          ;

          • Re: Calculating total charges from multiple transactions
            Srikanth P

            Firstly, you need a Key between these two tables and Join these tables and make one table like below:

            TRANSDATA:

            TransDate          ItemID          StatusID        AmountPaid

            02/01/15               1                    1                    -15.00  

            03/01/15               2                    1                    -55.00

            02/01/15               3                    1                    -25.00  

            02/03/15               1                    7                       7.00

            02/03/15               4                    1                    -45.00

            02/03/15               1                    10                    22.00

            19/03/15               3                    10                    55.00

             

            CLOSED_ITEMS:

            LOAD DISTINCT ItemID AS TEMP_ITEMID Resident TRANSDATA where StatusID = 10;

             

            CLOSED_DATA:

            LOAD ItemID,

                      ItemID AS ClosedItemID,

                      TransDate AS ClosedDate

            Resident TRANSDATE where exists (TEMP_ITEMID , ItemID) and StatusID = 10;

            Left Join

            Load ItemID ,

                    sum(AmountPaid) AS TotalCharges

            Resident TRANSDATE where exists (TEMP_ITEMID , ItemID)

            group by ItemID;

             

            DROP Table CLOSED_ITEMS;

             

            finally, CLOSED_DATA table having following data:

            ItemID   ClosedItemID   ClosedDate   TotalCharges

            1             1                  02/03/15         14

            3             3                  19/03/15         30

             

            and CLOSED_DATA and TRANSDATA tables linked on ItemID fields.

             

            Please post any sample data if you get my logic.

              • Re: Calculating total charges from multiple transactions
                Paul Edrich

                Hi Dathu

                 

                I changed your example to as Status ID in Closed_Items is not present in TRANSDATA - my amendments are:-

                LOAD DISTINCT
                ItemID AS TEMP_ITEMID
                Resident Satus where StatusID = 10;
                CLOSED_DATA:
                LOAD ItemID,
                         
                ItemID AS ClosedItemID,
                       
                StatusDate AS ClosedDate
                Resident Satus where exists (TEMP_ITEMID , ItemID) and StatusID = 10;
                Left Join
                Load ItemID ,
                       
                sum(AmountPaid) AS TotalCharges
                Resident Transactions where exists (TEMP_ITEMID , ItemID)
                group by ItemID;
                DROP Table CLOSED_ITEMS;

                I had previously attempted and now see the mistake - many thanks a great help :

                tmp:

                LOAD DISTINCT

                ItemID,

                StatusID as LatestStatus, 

                max(StatusDate) as LastStatusDate

                RESIDENT Satus

                GROUP BY ItemID;

                LEFT JOIN (Transactions) LOAD DISTINCT

                ItemID, 

                Sum(AmountPaid) as NetAmount

                RESIDENT tmp;

                DROP TABLE tmp;

                LEFT JOIN (AccountsTable) LOAD DISTINCT

                ItemID,

                StatusID,

                LastStatusDate,

                NetAmount,

                RESIDENT ClosedAccountsTable;