Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Filter 0 if there's row which have value more than 0

  1. #1

    Default Filter 0 if there's row which have value more than 0

    here's the case

    i've got a table which got 3 field
    1. id(varchar)
    2. state1(int)
    3. state2(int)
    i've set all of them as primary key, which mean combination of the three field should be unique

    the problem

    value of state2 ranged from 0 to 99, is there a way for me to filter row when the value of state2 is 0 and when there's another row with same id and state1 with state2 more than 0?.

    Note that there may be more than one value which is more than 0 and i hope them to stay that way.

    is it possible to achieve this in pentaho?

    Thanks in advance

  2. #2
    Join Date
    Nov 2009
    Posts
    672

    Default

    You can do that with a sql query:
    select * from table t1
    inner join table t2 on t1.id = t2.id and t1.state1 = t2.state1
    where t1.state2 = 0 and t2.state2 <> 0

  3. #3
    Join Date
    Apr 2008
    Posts
    4,639

    Default

    You could sort rows on ID and State1.
    Then feed it to a Group By step calculating the Max value of the group, returning all rows.

    If you do this, you would have:
    ID | S1 | S2 | MS2
    1 | 1 | 0 | 10
    1 | 1 | 1 | 10
    1 | 1 | 10 | 10

    You can then remove any row where S2=0 and MS2 <> 0

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 - 2017 Pentaho Corporation. All Rights Reserved.