3 Replies Latest reply: May 15, 2014 9:55 AM by Gareth Horton

# Duplicates filtering

Hi,

I've got a current problem with duplicates filtering. My database is as follows:

[font="courier"]Acc-Nr  Invoice-nr   Value

5000    1234892         300

1000    1234000         100

1000    5678000        -100

1000    9101100         200

1000    1213140         800

4000    8498795         50

4000    4568951        -50

4000    8546540         80

/font[/quote]No I would like to filter the same positive and negative amount of one account.

So I can see always which belongs to each other.

Can somebody give me an idea for this problem ?

Oli

• ###### Duplicates filtering

Are you looking for something that will return this:

[font="courier"]Acc-Nr  Invoice-nr   Value

1000    1234000         100

1000    5678000        -100

/font[/quote][quote][font="courier"]Acc-Nr  Invoice-nr   Value

4000    8498795         50

4000    4568951        -50

/font[/quote]Your filter will use the Absolute Function:

Abs() = # such as

Abs() = 100 'First Example

Abs() = 50  'Second Example.

Hope that helps!

Originally posted by Oli:

Hi,

I've got a current problem with duplicates filtering. My database is as follows:

[font="courier"]Acc-Nr  Invoice-nr   Value

5000    1234892         300

1000    1234000         100

1000    5678000        -100

1000    9101100         200

1000    1213140         800

4000    8498795         50

4000    4568951        -50

4000    8546540         80

/font[/quote]No I would like to filter the same positive and negative amount of one account.

So I can see always which belongs to each other.

Can somebody give me an idea for this problem ?

Oli /b[/quote]

• ###### Duplicates filtering

Originally posted by Oli:

So I can see always which belongs to each other.

/b[/quote]How can you be certain that the two values belong together?

Grant

• ###### Duplicates filtering

Oli,

I think what you want is this:

All the accounts that include duplicate credits and debits.

You can try this:

Create a calculated field called Value_Sign_Stripped, using the expression Abs(Value).

Create a filter, using only the options on the advanced tab.  The filter expression will automatically fill in as 1.

Select the 3rd option button - "Select all rows for which there are duplicate values for the specified keys.

Select Acc-Nr and Value_Sign_Stripped as keys.

The filter will return the following:

[font="courier"]Account     Invoice     Amount

1000     1234000     100

1000     5678000     -100

4000     8498795     50

4000     4568951     -50

/font[/quote]Gareth

Originally posted by Oli:

Hi,

I've got a current problem with duplicates filtering. My database is as follows:

[font="courier"]Acc-Nr  Invoice-nr   Value

5000    1234892         300

1000    1234000         100

1000    5678000        -100

1000    9101100         200

1000    1213140         800

4000    8498795         50

4000    4568951        -50

4000    8546540         80

/font[/quote]No I would like to filter the same positive and negative amount of one account.

So I can see always which belongs to each other.

Can somebody give me an idea for this problem ?

Oli /b[/quote]