Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Filter rows using "in list" with integers

  1. #1
    Join Date
    Dec 2016
    Posts
    27

    Default Filter rows using "in list" with integers

    Hi,

    I have a column with values 1, 2, 3 and <null>. It is cast as integer.

    Should I be able to filter in list with type integer and the Value 1;2?


    I get this conversion error message:

    &quot;Unexpected conversion error while converting value [constant String] to an Integer

    constant String : couldn&#39;t convert String to Integer

    constant String : couldn&#39;t convert String to number : non-numeric character found at position 2 for value [1;2;3]&quot;

    Thanks,



    Andy

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by mek View Post
    Should I be able to filter in list with type integer and the Value 1;2?
    No.

    Select type String in Filter-Rows and try again.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Dec 2016
    Posts
    27

    Default

    Interesting. I did try that, and while Pentaho let me save the list values as string it subsequently didn't filter my column. My column was cast as integer. I guess my column should also be cast as string for this to work then.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Instead of trying to do an "in", try chaining the the statements with OR blocks:

    Column = 1
    or
    Column = 2
    or
    Column = 3

    Alternatively, you can use a Validation step to check if the value is in a list of allowed values, rather than a filter step

  5. #5
    Join Date
    Dec 2016
    Posts
    27

    Default

    Great tip with the Data Validator step, thanks - I didn't know it existed

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    The original question was about the IN LIST operator, so here's an example of filtering an Integer field just to show we can.
    Attached Files Attached Files
    So long, and thanks for all the fish.

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.