# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor

## Calculating total charges from multiple transactions

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

1 Solution

Accepted Solutions
Not applicable

## Re: Calculating total charges from multiple transactions

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:

ItemID AS ClosedItemID,

TransDate AS ClosedDate

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

Left Join

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.

3 Replies
Not applicable

## Re: Calculating total charges from multiple transactions

Put your data into QVD and then :

Transdate AS ClosedDate

sum(AmountPaid) AS TotalCharge

From MyTable.qvd (qvd)

Group By TransDate
;

Not applicable

## Re: Calculating total charges from multiple transactions

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:

ItemID AS ClosedItemID,

TransDate AS ClosedDate

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

Left Join

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.

Contributor

## Re: Calculating total charges from multiple transactions

Hi Dathu

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

ItemID AS TEMP_ITEMID
Resident Satus where StatusID = 10;
CLOSED_DATA:

ItemID AS ClosedItemID,

StatusDate AS ClosedDate
Resident Satus where exists (TEMP_ITEMID , ItemID) and StatusID = 10;
Left Join

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:

ItemID,

StatusID as LatestStatus,

max(StatusDate) as LastStatusDate

RESIDENT Satus

GROUP BY ItemID;

ItemID,

Sum(AmountPaid) as NetAmount

RESIDENT tmp;

DROP TABLE tmp;