calculator column name issue

[JeansOn]

Hi,

I have an Excel-input. I have to filter the lines having "no" or "NO" ...

I think, I have to use the calculator-function "lower case of a String A" giving a new field "field_A_lower".
It works fine, but there is a second column with a long column-name having returns or new-line characters in it. The used filters of the transaction works with such long column definition, but the calculator doesn't.

I am rather new with pentaho, but I please you to help.

Error log:
2012/05/02 12:35:37 - Spoon - Transformation opened.
2012/05/02 12:35:37 - Spoon - Launching transformation [SWE_DRV_Abfrage_alle_test]...
2012/05/02 12:35:37 - Spoon - Started the transformation execution.
2012/05/02 12:35:37 - SWE_DRV_Abfrage_alle_test - Dispatching started for transformation [SWE_DRV_Abfrage_alle_test]
2012/05/02 12:35:37 - Transformation metadata - Natural sort of steps executed in 0 ms (20 time previous steps calculated)
2012/05/02 12:35:38 - SWE_DRV_Abfrage_alle_test - SWE_DRV_Abfrage_alle_test
2012/05/02 12:35:38 - SWE_DRV_Abfrage_alle_test - SWE_DRV_Abfrage_alle_test
2012/05/02 12:35:40 - Spoon - The transformation has finished!!
2012/05/02 12:35:40 - SWE_DRV_Abfrage_alle_test - ERROR (version 4.3.0, build 16295 from 2012-01-27 15.53.26 by tomcat) : Errors detected!
2012/05/02 12:35:40 - SWE_DRV_Abfrage_alle_test - ERROR (version 4.3.0, build 16295 from 2012-01-27 15.53.26 by tomcat) : Errors detected!

[darrell.nelson]

I've never experienced that error with the Calculator Step before but a case insensitive compare can be done directly in the filter rows step without having to calculate another field. You can use the Filter Rows Step using the REGEX compare operator with the following compare string:

(?i)no

and it will match no and NO, and even No and nO.

[Brian]

Not sure I fully understand the issue, but one way to make fields lowercase:

Using the java expression step

New field: fieldname
Java Expression: fieldname.toLowerCase()
value type: String
Replace value: fieldname

Where fieldname is the field you wish to change to lowercase.

[darrell.nelson]

What I am saying is that you don't have to make a copy of a field and convert it to lower case in order to have a case insensitive comparison in the Filter Rows Step. Instead of using the "=" compare operator and "no" for the compare string, use the "REGEXP" compare operator and "(?i)no" compare string as shown below. The "(?i)" at the beginning makes the comparison case insensitive so it will send both "NO" and "no" to the 'true' step.

CaseInsensitivity.jpg

[JeansOn]

Now, thank you for your valuable replies. The discuss has shown the way. I didn't think I'd to replace the compare operator. But a test works now and so I'll change the whole transformation.

Thank you,
JeansOn