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

    Duplicates filtering

    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

        • Duplicates filtering
          Nick Osdale-Popa

          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
            Grant Perkins

            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
              Gareth Horton

              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]