3 Replies Latest reply: May 15, 2014 9:55 AM by Dee Moore RSS

    Filter using Len

    kentb _

      Hi.

      I am trying to filter my data to say if column 2 has 9 digits and column 4 is Null do not show data.

      Any suggestions?

        • Filter using Len
          Grant Perkins

          What are the options for column 2?

           

          Can it have less than 9 digits and/or more than 9 digits?

           

          aolso, just in case it becomes relevant, which version of Monarch are you using?

           

           

          Grant

          • Filter using Len
            Dee Moore

            Hi,

             

            Some Filters can be a bit tricky to produce. The order of the Arguments in the Expression can have as much of an affect as the Arguments themselves. And, when you are trying to use mixed Datatypes and Compound the Filter by adding an Operator, sometimes nothing you do seems to work.

             

            When I come across a situation like this, I find it a little easier to create a new Calculated Field that will identify the data I'm trying to filter. Then create a Filter using the new field.

             

            If Column2 is a Numeric datatype, you will first need to change the datatype to Character in order to work with the Len() function. This field may also need to be LTrimmed, so I'd create a new Calculated field:

             

            New_Column2

             

            LTrim(Str(Column2,20,0,))

             

            Now you have a new Character Field containing your Column2 data which the Len() function can be used on.

             

            Now create another Calculated Field with a Character datatype that you can enter your criteria and produce a result you can use to Filter on:

             

            New_Filter_Field

             

            If(IsNull(Column4) .And. Len(New_Column2)=9,"Filter","")

             

            This created a new Calculated field that contains the word Filter if the length of the New_Column2 field is equal to 9 and the Column4 field is Null.

             

            Now you can create a simple Filter to produce the result you need:

             

            GoodDataFilter

             

            New_Filter_Field<>"Filter"

             

            Hide the Calculated Fields you created and Save your Model.

             

            I hope this is helpful.

             

            Dee Moore

            Datawatch Tech Support

            • Filter using Len
              Dee Moore

              I checked this thread and realized I could have been a bit more clear.

               

              My response was geared toward creating a visual mechanism for Users to display a field identifying the data the Expression will affect so they may better understand how Operators work within Filters.

               

              The Filter Expression:

               

              If(IsNull(Column1) .And. Len(Column2)=9,IsNull(Column1)=0,Column2)

               

              Will work just as well without any Calculated Fields.

               

              Dee Moore

              Datawatch Tech Support