Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Filter Row - case insensitive?

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Filter Row - case insensitive?

    Hello Everyone,

    I wonder if it is possible to make filter row component case insensitive?

    Regards,
    Alex

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    If you are filtering against a constant value, the regex prefix (?i) should turn on case insensitivity for the remainder of the expression.
    Last edited by darrell.nelson; 11-10-2011 at 09:07 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    If you need to compare two fields, add a User Defined Java Expression Step with a Java expression of Value2.equalsIgnoreCase(Value1) to create a boolean match field. You can then just use the match field for your comparison in the Filter Rows step. That function is defined in the java.lang.String class. Just make sure that Value2 isn't null or the expression might blow up. Something like this should do the trick: Value2 == null ? false : Value2.equalsIgnoreCase(Value1) i think.

    ...or just use the freaking Java Filter step with that same expression! I never knew there was such a thing.
    Last edited by darrell.nelson; 11-10-2011 at 09:14 PM. Reason: Ignorance
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    You should create a JIRA requesting that feature in the Filter Rows step. I suspect it would be fairly easy to add an "EQUALS IGNORE CASE" function and I think it would be useful to many.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Sep 2011
    Posts
    171

    Default

    Quote Originally Posted by darrell.nelson View Post
    If you are filtering against a constant value, the regex prefix (?i) should turn on case insensitivity for the remainder of the expression.
    Okay, regex did not work for me. I attached an example, not sure what I have done wrong.

    Let me try to investigate other options.

    I have very little programming experience, so I am confused how to use Java Filter and basic google search does not help.

    Regards,
    Alex
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2011
    Posts
    171

    Default

    Quote Originally Posted by darrell.nelson View Post
    If you need to compare two fields, add a User Defined Java Expression Step with a Java expression of Value2.equalsIgnoreCase(Value1) to create a boolean match field. You can then just use the match field for your comparison in the Filter Rows step. That function is defined in the java.lang.String class. Just make sure that Value2 isn't null or the expression might blow up. Something like this should do the trick: Value2 == null ? false : Value2.equalsIgnoreCase(Value1) i think.

    ...or just use the freaking Java Filter step with that same expression! I never knew there was such a thing.
    Do you think you can look at my example. What am I doing wrong?
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2008
    Posts
    777

    Default

    From all my ramblings yesterday I can see why, but you are using a hybrid of the two options I recommended. From the file I downloaded, you are using the UDJE to create the match field but the Filter Rows step is not set to evaluate the match field. It should be set to "match = Y".

    What you do have set in your Filter Rows step is "Name CONTAINS (?i)table". If you are going to use this method it should be "Name REGEXP (?i)table" and the UDJE step would not be needed.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by august_month View Post
    I have very little programming experience, so I am confused how to use Java Filter and basic google search does not help.
    In Spoon in the Flow group, there is a step called Java Filter. You can use that to totally replace the UDJE step and the Filter Rows step. Connect your two output steps to it and put the expression Name.equalsIgnoreCase(NameDiffCase) in the Condition box. What it does is combine two steps in one so you don't have to create a match field.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  9. #9
    Join Date
    Sep 2011
    Posts
    171

    Default

    Quote Originally Posted by darrell.nelson View Post
    .

    What you do have set in your Filter Rows step is "Name CONTAINS (?i)table". If you are going to use this method it should be "Name REGEXP (?i)table" and the UDJE step would not be needed.
    You are life saver. The following worked .*(?i)table.*

    Regards,
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.