5 Replies Latest reply: May 15, 2014 10:02 AM by Guy Chowning RSS

    Filter Based on Upper or Lower Case

    Guy Chowning

      I have a set of data that includes a "TYPE" field. It is a single digit field which may contain 0 thru 9 , upper case A thru Z, or lower case a thru z. The case is important in determining the type of transaction. For example an upper case F , means it is an Invoice for Freight whereas a lowercase f, means it an estimated accrued freight amount.

       

      Is there any way to create a filter which will allow me to filter based on the case?

       

      Any ideas will be greatly appreciated!

        • Filter Based on Upper or Lower Case
          Grant Perkins

          Guy,

           

          I think you will need to use the ASC() function and filter based on the ASCii value of the character rather than the character itself. See the Help for an example, albeit it not directly related to alpha characters.

           

          Somewhere in the downloads section of the Datawatch web site you should be able to find a file called ASCII.txt that will provide a table of characters and values - assuming you don't already have one. It certainly used to be available - have not looked for it for a while.

           

          Failing that these things should be available widely on the net. If any unusual characters are used take car - there can be variations once youget outside the 'standard' set of characters.

           

          HTH.

           

           

           

          Grant

          • Filter Based on Upper or Lower Case
            Data Kruncher

            Hi Guy,

             

            According to the online help (look up case-sensitiivity), "Comparisons performed within expressions are not[/I] case-sensitive."

             

            So when filtering, Monarch interprets "f" as equivalent to "F", and that's no good for your needs.

             

            So we need come at it from a slightly different angle. In this case we'll take advantage of ASCII values.

             

            For example, a filter expression for Invoice for Freight will be:

            Asc(TYPE)=Asc("F")[/code]

             

            whereas the expression for Estimated Accrued Freight Amount will be:

            Asc(TYPE)=Asc("f")[/code]

             

            You'll need to carefully construct the various filters watching the case of the letters as you go (the numeric values don't have the case problem), but that'll do it.

             

            Kruncher

             

            Edit: Grant beat me to the submit button this time around. At least we had the same approach in mind.

              • Filter Based on Upper or Lower Case
                Bruce _

                There also a possibility to trap on lower or upper case separately. There is an option on the input options, (options, input, Traps are case sensitive"). If you do this you can have a trap for "f" different from "F".

                 

                However I expect it would be easier to follow Grant & Kruncher's suggestion for a filter rather than a trap, but wanted to raise another suggestion.

                 

                Cheers

              • Filter Based on Upper or Lower Case
                Nigel Winton

                Yet another suggestion, use the IsUpper function to determine the case of the character or the IsLower function, depending on which way you lean. Then other calculated fields to get you results.

                 

                 

                Nigel