Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeffrey_a_lin
Partner - Contributor II
Partner - Contributor II

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:

10005Credit-$300
10005Debit$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.

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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)

Untitled.png

View solution in original post

3 Replies
Thiago_Justen_

So, what about aggr by flag?

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
jeffrey_a_lin
Partner - Contributor II
Partner - Contributor II
Author

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

kaanerisen
Creator III
Creator III

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)

Untitled.png