5 Replies Latest reply: May 15, 2014 9:55 AM by Awol _ RSS

    Filtering Excludes Amounts Below £1

    Awol _

      I have a straightforward filter applied to a charcter field (Receipts .And. Ref="T**").

       

      It works other than deciding to exclude lines with value below £1 i.e 0.01 or -0.20. These amounts appear prior to the application of the filter.

       

      Anyone know why it would ignore these amounts when the character part complies with the filter rules?

        • Filtering Excludes Amounts Below £1
          Dee Moore

          Hi Awol,

           

          First:

           

          The Receipts field does not have a condition in the Filter Expression (ie: = , < , > , etc).

           

          Receipts .And. Ref="T**"

           

          If the field is not providing any instruction to the Expression, it should not be there.

           

          Second:

           

          The * is the Wildcard for "All", and in many instances it is necessary to use its ASCII Value in Expressions to return the correct results. If you do not have an ASCII Chart, the Asc() function will return the ASCII value of any character for you. Simply create a new Numeric Calculated Field.

           

          The Expression Ref = "T*" is instructing Monarch to return any line that begins with a "T" as long as it has data after it. It doesn't matter if you have one "", or fifty..........

          The only records Filtered out are those that start with something other than "T" and those that only have "T" in the first position and nothing else.

           

          If you need to return only records that start with a "T" and have two asterisks in succession after it, one correct way to write the Expression would be:

           

          Ref="T*" .And. Asc(Substr(Ref,2,1))=42 .And. Asc(Substr(Ref,3,1))=42

           

          Another easy one is:

           

          Instr("T**",Ref)

           

          Instr("T*",Ref)=0 if you do not want to display cells that contain "T*".

           

          I hope this is helpful.

           

          Dee Moore

          Datawatch Tech Support

           

          [size="1"][ April 03, 2006, 06:23 PM: Message edited by: Dee Moore ][/size]

          • Filtering Excludes Amounts Below £1
            Awol _

            Thank you

             

            Ref="T*" on it's own did the correct filtering job on the T's but I think this will also pick up payments as well as receipts should they appear.

             

            I need to deal with the receipts column and the payments column seperately. What would filter out the unwanted column.

            • Filtering Excludes Amounts Below £1
              Grant Perkins

              Originally posted by Awol:

              I need to deal with the receipts column and the payments column seperately. What would filter out the unwanted column. /b[/quote]So are you now seeing the full values of the fields?

               

              For your current question I think we need to know how your source data looks and how you are capturing it.

               

              If you are capturing the payments into one column in your Monarch table and the Receipts into another you should only need to filter based on whether there is a value in the column you wish to analyse.

               

              If the values are all in one field in the Monarch Table you would, presumably, need another column that identifies what sort of transaction it is  - so you would filter on that.

               

              Or maybe the value itself is signed to indicate whether it is a payment or receipt - perhaps a minus sign or something?

               

              If you have Monarch version 8 you could build the separate filters and check that they work indiviudally for what you want them to do and then combine them using the Compound Filter functionality. It would make the creation, testing and flexibility of filter usage that much easier.

               

              If none of the above situations match what you have are you in a position to post a sample of your data format so that we can try to provide more specific advice?

               

              HTH

               

               

              Grant

              • Filtering Excludes Amounts Below £1
                Dee Moore

                Hi Awol,

                 

                If you want to see/work with only one COLUMN at a time.

                 

                Building two Summary Tabs and associating the appropriate Filter with each should produce this for you.

                 

                If you prefer to work in the Table Window Hide/Display the fields you need and choose the appropriate Filter.

                 

                If you need to Filter two columns while they are both displayed, the Compound Filter, or even a simple Filter using an Operator (.AND., .OR.) should work nicely.

                 

                If you do need to build a more Complex Filter, our most recent Monarch Report:

                 

                [url="http://www.datawatch.com/pdf/products/monarch/Mar06_Report_Final.pdf"]http://www.datawatch.com/pdf/products/monarch/Mar06_Report_Final.pdf[/url]

                 

                contains a helpful tip that may help lead you in the right direction.

                 

                Regards,

                 

                Dee Moore

                Datawatch Tech Support

                • Filtering Excludes Amounts Below £1
                  Awol _

                  All I did was reverse the original from

                   

                  Receipts .And. Ref="T*"  to   Ref="T*" .And. Receipts

                   

                  Seems to work now. Still not sure why the original would pick up everything other then amounts below £1