Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: How to use In LIST with integers

  1. #1

    Default How to use In LIST with integers

    I am trying to use a list on an integer column, but there appears to be no way I can define a list of integers. When I select INTEGER as the value type and then enter e.g. 4;6;9;11;, I get a parse error. What do I need to do to make this work?
    If I set the value type to String, the list gets accepted (I suspect, though ,as string). When I then try and run the transformation I get this output in the log:

    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : java.lang.RuntimeException: Unexpected error evaluation condition [(
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : (
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Month IN LIST []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : AND Day <= []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : )
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : OR
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : (
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Month = []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : AND Day <= []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : )
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : OR
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : (
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Month = []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : AND Year IN LIST []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : AND Day <= []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : )
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : OR Month IN LIST []
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : )
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : ]
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : at org.pentaho.di.core.Condition.evaluate(Condition.j ava:521)
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : at org.pentaho.di.trans.steps.filterrows.FilterRows.k eepRow(FilterRows.java:49)
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : at org.pentaho.di.trans.steps.filterrows.FilterRows.p rocessRow(FilterRows.java:89)
    2007/10/22 15:30:22 - Filter rows.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : at org.pentaho.di.trans.steps.filterrows.FilterRows.r un(FilterRows.java:146)


    clearly indicating that the parser didn't understand my intentions. So what to do?

  2. #2

    Default

    Can u attach a sample Transformation?

    I'm not sure if I do really the same, but it seems that it works for me on 3.0.0-M2

  3. #3

    Default

    XML file is attached.
    I am running 3.0.0 RC-1, if that helps.
    Attached Files Attached Files

  4. #4

    Default

    Sorry, ignore last reply. The attachment was invalid, as it did not have the correct filter values inside. Try this one.

    It looks to me like IN LIST doesn't work at all. According to other posts, the individual values in the list must be separated by semicolon. Therefore a valid list for a number of integer values would like this : 4;6;9;11;
    However, if I select a value type of Integer or Number, it won't parse the string correctly. The only thing that passes the test is a String, but I suspect that in this case it does not interpret it as a value list, but simply as the string entered.
    When I select the type as String and then run the transformation, it does not find any values for the IN LIST operator and I end up with the empty value list as shown in my first post. The manual isn't really helpful, as it does not explain the required syntax for the IN LIST operator.
    Attached Files Attached Files
    Last edited by wolfgangsz; 10-24-2007 at 06:17 AM.

  5. #5

    Default

    I just looked at the transformation for a short moment an started a preview on "Create Dates" step.

    it seems, that the values are left padded with 0. maybe that's the Problem?

    In 3.0.0 M2 it works fine.

    I've attached screenshots of the preview in RC1 and M2
    Attached Images Attached Images   

  6. #6

    Default

    Hi Michael,

    it's not the "Create Dates" step that is causing the problem, it is the subsequent "Filter invalid dates" step that falls over. Have you tried that one?
    Also, if I do a preview up to any step, there is not problem. But when I click on Run, that's when the filter falls over.
    Last edited by wolfgangsz; 10-24-2007 at 07:01 AM.

  7. #7

    Default

    Yes i know. But it seems, that the IN LIST comparator handles the values as strings.

    So the condition "Month IN LIST [4;6;9;11]" compares the String " 000000004' with "4" and that will never match.

    I've inserted a "select rows" step which formats the values to integer of length and precision of "-1".
    Now, the values aren't leaded by zeros any more, but there is a blank at the first position for positive values. For negative values there is a "-" at the first position. Not sure if the blank is a bug.

    After that the modified (see screenshot) "Filter rows" works for me.

    I don't know if it's a bug or a feature, that u cannot use Integer values for the "IN LIST" comparator. I'm just a user too ;-)
    Attached Images Attached Images   

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The real answer would probably be that nobody tried it until now.
    We are fixing a number of compatibility issues with respect to the more consistent data-type-to-String conversion we do in 3.0RC2.
    Apparently it was kind of a mess in 2.5.2 so we need to apply some compatibility code here and there.
    Every time you're using implicit data conversion you're hit by this.
    In the case of the LIST operator we compare Strings, not the native data type.
    This is hurting us since we pad with 0's as you noted above.

    It's one more item I'm fixing for PDI-352 : http://jira.pentaho.org/browse/PDI-352

    Matt

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Please help out by testing my compatibility fix : http://s3.amazonaws.com/kettle3/Kett...2-20071025.zip

    Thanks in advance,

    Matt

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.