Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to filter rows that comes from another step

  1. #1

    Default How to filter rows that comes from another step

    Hi everyone,
    I would like to know how to filter rows that the data come from Table input step.
    I have a Table input step that contain a query with union all statement, the query result have column1, column2. So i want to get a max() of column2. That the result of this transformation is the column2 with max() data.
    I tried several ways to do it, but I couldn't.

    Any help, will be appreciated.

    rgs
    Dimy

  2. #2

    Default

    This is not really a filter - its an aggregation/grouping. If you look ahead with this keyword - you will find for example the Group By as solution, or some other step from "Statistics". Or you can do it directly with SQL (use your union all as derived query).

  3. #3

    Default

    I have a query:

    SELECT column1, column2 FROM A
    UNION
    SELECT column1, column2 FROM B;

    The result of the query is:
    column1 column2
    id1 y
    id1 n
    id2 n
    id3 n
    id4 y

    but the result must be:
    column1 column2
    id1 y
    id2 n
    id3 n
    id4 y

    using sql i have to create a new query that i can use max() funtion.

    How can i do with kettle.

    thank your advice

  4. #4
    Join Date
    Oct 2007
    Posts
    107

    Default

    Why do you absolutely want to do it in PDI?
    Considering it's a SQL step, I don't see any reason why you would not do it directly in there such as:

    Select Column1, Max(Column2) As Column2
    From
    (
    SELECT column1, column2 FROM A
    UNION
    SELECT column1, column2 FROM B
    ) As MyQuery;



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

    Default

    Quote Originally Posted by CHamel View Post
    Why do you absolutely want to do it in PDI?
    Hear, Hear!

    And add a GROUP BY to that statement ...
    Code:
    Select Column1, Max(Column2) As Column2
    From
    (
    SELECT column1, column2 FROM A
    UNION
    SELECT column1, column2 FROM B
    ) As MyQuery
     GROUP BY Column1
    
    Last edited by marabu; 01-17-2014 at 09:01 AM. Reason: typo
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Oct 2007
    Posts
    107

    Default

    Oops, damn right Marabu

  7. #7

    Default

    Thank you.

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.