Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

solomon_musayev
New Contributor III

Remove Records that have Credit and Debit

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 #TestUnit CountCharge
111CT 200.001
111CT-200.00-1
112MRI100.001
112MRI-100.00-1
112MRI100.001
113CT200.001

The result I am looking for is the following

Acct #

Test
Unit Count

Charge
112MRI1001
113CT2001

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,

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Remove Records that have Credit and Debit

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;

4 Replies
Luminary
Luminary

Re: Remove Records that have Credit and Debit

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;

Luminary
Luminary

Re: Remove Records that have Credit and Debit

solomon.musayev is this working for you?

solomon_musayev
New Contributor III

Re: Remove Records that have Credit and Debit

Yes it did Spasibo.

Luminary
Luminary

Re: Remove Records that have Credit and Debit

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

Community Browser