topic Calculating total charges from multiple transactions in QlikView

Calculating total charges from multiple transactions

pauledrich — Thu, 19 Dec 2013 14:17:11 GMT

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

Thu, 19 Dec 2013 14:23:36 GMT

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

Thu, 19 Dec 2013 15:20:09 GMT

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

pauledrich — Thu, 19 Dec 2013 15:55:56 GMT

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;