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!

[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

[Data Kruncher]

Hi Guy,

According to the online help (look up case-sensitiivity), "Comparisons performed within expressions are not 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")

whereas the expression for Estimated Accrued Freight Amount will be:
Asc(TYPE)=Asc("f")

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.

[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

[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

[Guy Chowning]

It is wonderful to have the support you find here on the forum, sometimes my mind just goes blank.

Thanks Everyone