Identify Duplicate entries & additional clauses

[jeffrey_a_lin]

I wanted to inquire if there's a expression code to test duplicate entries, test whether the duplicates has a flag with 1 'Debit' & 1 'Credit' and their value is the inverse of each other.

Invoice Number	Flag	Value
10001	Debit	$1000
10001	Debit	$1000
10003	Debit	$5000
10004	Debit	$8656
10005	Credit	-$300
10005	Debit	$300
10006	Debit	$563

Expected results:

10005	Credit	-$300
10005	Debit	$300

Could this be done with a Variable & Expression code as I don't have access to reload the data.

I've attempted to use

Sum(Aggr(If(Count([Invoice Number)>1,1,0),[Invoice number]))

This does find duplicates but only shows 1 entry and omits the other.

[Thiago_Justen_]

So, what about aggr by flag?

Sum(Aggr(If(Count([Invoice Number)>1,1,0),[Flag]))

[jeffrey_a_lin]

Unfortunately that didn't work.

I tried another approach, although it doesn't look pretty and doesn't quite work.

I created these variables

Variable - CombinedAR

if((-Sum({$<[Flag]={'Credit'}>}[Value]) + Sum({$<[Flag]={'Debit'}>}[Value]))=0,'True','False')

Variable - DuplicateAR

Sum(Aggr(If(Count([Invoice Number])>1,1,0),[Invoice Number]))

I then created a column 'Transaction'

if($(DuplicateAR)=1 AND $(CombinedAR)='True',1,0)

Invoice Number	Flag	Value	Combined Value = 0	Duplicate Invoices	Transactions
10001	Debit	$1000	False	0	0
10001	Debit	$1000	False	0	0
10003	Debit	$5000	False	0	0
10004	Debit	$8656	False	0	0
10005	Credit	-$300	True	1	1
10005	Debit	$300	True	1	1
10006	Debit	$563	False	0	0

Not the most efficient method, but I guess it produces the result, unless someone can recommend a more streamline expression code

[kaanerisen · Accepted solution]

Hi Jeffrey,

Is this what you are looking for?

Script:

load * Inline [

IN,FLAG,NUMBER

10001,Debit,1000

10001,Debit,1000

10003,Debit,5000

10004,Debit,8656

10005,Credit,-300

10005,Debit,300

10006,Debit,563

];

Expression:

Sum({<IN={"=SUM(AGGR(SUM(NUMBER),IN))=0"}>}NUMBER)