Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: How to load only the Maximum value from a set of records?

  1. #1

    Default How to load only the Maximum value from a set of records?

    Hi All,

    I am loading data from a XML file to a Database table. I want to give the following Check while loading:
    Only the record with maximum date of birth should be loaded. Other records I need to reject or move to another table.

    I did sort row and group by for the column DOB and then checked the result from Group by with the stream field of DOB using Filter. After that, I branched the conditions with two inserts. But I am not getting the other values from the files to load in to the table.

    Following is the error message I get:
    2007/09/05 15:57:22 - Top Insert.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04): Field [Brand_id1] is required and couldn't be found!

    Is there any way to do this? Please help.

    Thanks,
    Darius

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Easiest is a sort and then only pick the first row... a variation of your yesterday's question.

    For your error ... preview each step before you hit the problem, you will probably the Brand_id1 field is not in your input

    Regards,
    Sven

  3. #3

    Default

    What if we want to load only the values less or greater than the average of a column? In this we need to Group By it riht? I am finding problem in group by. Could you help in that?

    Thanks,
    Darius

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Group by works more or less like an SQL group by, you want to have a kind of "having clause" which is not in.

    What you could do is calculate the average seperately, join that with the regular input and then select only the rows above or below your average.

    Regards,
    Sven

  5. #5

    Default

    How could i calculate the average seperately? If i do it in the steam, rest of the values are not getting populated. I am getting only the group by values as you said. Please give an example if possible.

    Thanks,
    Darius

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Calculate in 1 stream the average (which should then be 1 row).
    Join that stream with the normal stream use the cartesian join step.

    Regards,
    Sven

  7. #7

    Thumbs up

    Thanks Sven, Its working...


    Darius

  8. #8

    Default

    Easiest is a sort and then only pick the first row... a variation of your yesterday's question.

    I saw this reply can you please give me a code sample on how to do this.

    Thanks

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.