Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pauledrich
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:

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.

3 Replies
Not applicable

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
;

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:

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
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:-

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;

Community Browser