Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: PDI - Step Filter rows doesn't work

  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Default PDI - Step Filter rows doesn't work

    Hi,

    I have a problem with step "filter rows".
    I use PDI 4.2.0 enterprise version, and Database on Mysql.

    I want to compare 2 tables on a field in Datetime format like :
    table1.date1 >= table2.date2

    But the result is exactly what I expected :
    table1.date1 = "2011-01-01 00:00:00"
    table2.date2 = "2001-01-01 00:00:00"
    => false

    If I used a value instead of table2 it work...

    Does anyone can help me ?

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    I would check if those fields are set as "Datetime" and have the correct format within PDI.

    Mick

  3. #3
    Join Date
    Oct 2011
    Posts
    10

    Default

    PDI considered Datetime like Date, but no mask is defined automatically
    So I try to force format with mysql date_format() function but in these case PDI considered date like String...

  4. #4
    Join Date
    Feb 2009
    Posts
    321

    Default

    weird.... try to use metadata, and change the format.

  5. #5
    Join Date
    Oct 2011
    Posts
    10

    Default

    try to use metadata, and change the format.
    I'm new in a pentaho's world, I suppose u want I use "metadata structure of stream" step ?
    If it's correct, those step return same things : DATE, with -1 for length and precision

    If comparison of Date doesn't work, maybe I can convert data into number, but I don't know what step using for doing that. Any idea ?

  6. #6
    Join Date
    Feb 2009
    Posts
    321

    Default

    you could use java script to transform date to a number, use first the function date2Str and for last the function str2Num

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

    Default

    Quick question...

    What are Table1.date1 and Table2.Date2?

    Are you doing a Join in your table input?
    eg:
    Table1.Date1 | Table2.Date2
    2011-01-01 00:00:00 | 2001-01-01 00:00:00

    Or are you doing a straight select?
    Date
    2011-01-01 00:00:00
    2001-01-01 00:00:00

    If you are using the filter rows, you need to somehow get the data so that it is more like the first example:
    Table1.Date1 | Table2.Date2
    2011-01-01 00:00:00 | 2001-01-01 00:00:00
    So that you can compare columns **WITHIN THE ROW**

    Try making a Simple Sample for us so we know what you are doing... Replace your Table Input steps with Data Grids that provide similar data sets to what you get from your Table Input (eg. If your Table Input is "Select Date1 from Table1" then create a Data Grid that gives a column Date1 with realistic values)

    If you then post this sample transformation, then the community can help point you in the right direction.

  8. #8
    Join Date
    Oct 2011
    Posts
    10

    Default

    Sorry for delay

    @gutlez : straight select

    I want to compare 2 tables on 2 streams :

    table1 (column date1) => lots of rows
    2011/10/03 08:00:00
    2011/10/04 09:00:00

    and table2 (column date2) => Just one row
    2011/10/03 10:00:00

    I used filter rows: table1.date1 > table2.date2
    I want to obtain just one row in this exemple:
    2011/10/04 09:00:00

    But it's not.
    So I understand that: If I want to compare 2 values with the same type like datetime, It's necessary to use metadata step to force the format...

    PS: Before answer, I change my job explain to get first value of table2 and I use it in a second step directly in SQL, and it's work.

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.