Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Aggreagte Rows per SQL: Only within database?

  1. #1
    Join Date
    Jun 2009
    Posts
    13

    Default Aggreagte Rows per SQL: Only within database?

    Hello Forum,

    I have a Excel Sheet with 5 Columns: LastName, Firstname, Title, City, Earning. Now I want to aggreagte the earnings per person.
    SQL Stmt: Select Title, LastName, Firstname, sum(Earning) From ??? Group By Title, LastName, Firstname;

    Can I do this aggregation without writing the Excel rows to a database? (Maybe Kettle has the ability to use a mysql database without creating manually the table/columns.) Or do I have to write the Excel data to the database and get them back with an SQL stmnt like above?


    Thanks

    Peter

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

    Default

    Have a look at the group by step

    Regards,
    Sven

  3. #3
    Join Date
    Feb 2009
    Posts
    321

    Default

    you can do it in both ways..

  4. #4
    Join Date
    Jun 2009
    Posts
    13

    Default

    @sboden: Thanks, ot worked.

    @hernanthiebaut: Can you give me a hint how I could use the db engine and SQL abilities without manualy creating the cols/tables? What kind of transformation must I use?

  5. #5
    Join Date
    Feb 2009
    Posts
    321

    Default

    yes of course..
    this example consists in 2 transformation and one job.
    1st trans:

    step excel input,-->select values --> table ouput

    excel input = is your input file
    select values = mapping or eliminate values
    table output: name of table and schema of db. if the table is not created, this step contains an option "sql", this option have the script (create table ...)

    2nd trans:

    table input --> here execute the "select value_1 from ... group by ...."

    The following steps depend on what you want done


  6. #6
    Join Date
    Jun 2009
    Posts
    13

    Default

    Thanks hernanthiebaut

    One short question: The strings within the database have a fixed length. I I read the data I get big strings with a lot of spaces at the end. Is there a transformation with trim, like the input text files? Or do I have to create a JavaScript that does this?

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

    Default

    Which database engine?

    You should be able to TRIM() the fields on the table input...

  8. #8
    Join Date
    Jun 2009
    Posts
    13

    Default

    Thanks Gutlez, you are right, there is a TRIM() within SQL. The Table Input transformation doesnt offer a trim().

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

    Default

    If you do:

    Code:
    Select TRIM(Title), TRIM(LastName), TRIM(Firstname), sum(Earning)
    GROUP BY TRIM(Title), TRIM(LastName), TRIM(Firstname)
    in the Table Input, it *SHOULD* work, but it likely won't be efficient.

    An improvement would be to convert the columns from fixed width in the DB to variable length

    I expect that one of the more knowledgeable contributors will be able to improve it.

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.