3 Replies Latest reply: Jun 20, 2018 6:58 AM by kaan erisen

# Identify Duplicate entries & additional clauses

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 NumberFlagValue

10001

Debit\$1000

10001

Debit\$1000
10003Debit\$5000
10004Debit\$8656
10005Credit-\$300
10005Debit\$300
10006Debit\$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.

• ###### Re: Identify Duplicate entries & additional clauses

So, what about aggr by flag?

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

• ###### Re: Identify Duplicate entries & additional clauses

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 NumberFlagValueCombined Value = 0Duplicate InvoicesTransactions

10001

Debit\$1000False00

10001

Debit\$1000False00
10003Debit\$5000False00
10004Debit\$8656False00
10005Credit-\$300True11
10005Debit\$300True11
10006Debit\$563False00

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

• ###### Re: Identify Duplicate entries & additional clauses

Hi Jeffrey,

Is this what you are looking for?

Script:

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)