Remove Records that have Credit and Debit

[Anonymous]

Hello

I have a charges table with certain products having charge made that was subsequently canceled.  I need to remove those records from the data set. 

The table look something like this 

Acct #	Test	Unit Count	Charge
111	CT	200.00	1
111	CT	-200.00	-1
112	MRI	100.00	1
112	MRI	-100.00	-1
112	MRI	100.00	1
113	CT	200.00	1

The result I am looking for is the following

Acct #	Test	Unit Count	Charge
112	MRI	100	1
113	CT	200	1

The Acct#  111 needs to be completely removed and Acct #112 needs to have only 1 record and Acct #113 should be present.

I have tried using the sum function in the load editor but its not giving me the result Im looking for.  I might be doing it wrong.

Please help me or point me to the right direction.

Thank you,

[Ivan_Bozov · Accepted solution]

Hello! Can you try the following:

Table1:
LOAD * INLINE [
Acct,Test,Unit Count,Charge
111,CT,200,1
111,CT,-200,-1
112,MRI,100,1
112,MRI,-100,-1
112,MRI,100,1
113,CT,200,1
];

LEFT JOIN LOAD
    Acct,
    Sum(Charge) AS SumCharge
RESIDENT Table1
GROUP BY Acct;

Table2:
LOAD
    *,
    IF([Unit Count]>0, 1, -1) AS Charge;
LOAD
    Acct,
    Test,
    SUM([Unit Count]) AS [Unit Count]
RESIDENT Table1
WHERE SumCharge <> 0
GROUP BY Acct,Test;
DROP TABLE Table1;

[Ivan_Bozov]

solomon.musayev is this working for you?

[Anonymous]

Yes it did Spasibo.

[Ivan_Bozov]

By the way, line 21 of the script should be:

IF([Unit Count]>0, 1, -1) AS Charge;

Even though I first edited the original comment, the change was not saved...