Calculating total charges from multiple transactions

[pauledrich]

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

[#2]

Put your data into QVD and then :

LOAD

Transdate AS ClosedDate

sum(AmountPaid) AS TotalCharge

From MyTable.qvd (qvd)

Group By TransDate
;

[#3 · Accepted solution]

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.

[pauledrich]

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;