Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
So, what about aggr by flag?
Sum(Aggr(If(Count([Invoice Number)>1,1,0),[Flag]))
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
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)