Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Put your data into QVD and then :
LOAD
Transdate AS ClosedDate
sum(AmountPaid) AS TotalCharge
From MyTable.qvd (qvd)
Group By TransDate
;
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.
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;