US and Worldwide: +1 (866) 660-7555
Results 1 to 7 of 7

Thread: How to make a pivot table?

  1. #1
    Join Date
    Jan 2011
    Posts
    6

    Default How to make a pivot table?

    Hello,

    I am trying to build a pivot table from my source and then export it to excel. I do not want the pivot table to be made in excel, but it be fixed based on output from transformation. I've been looking around in Kettle and only found the "Row denormalizer" step that looked similar. However it seems to be pretty much fixed (columns need to be predefined, which I don't want). Any other way to do it that I would have missed?

    I have a table with columns products, months and prices. My output table should have all products in rows, months in columns, and the related price at intersection. Every month new month will come in, and I want it to automatically add a column in my table. It doesn't sound so complex at first...

    Thanks,
    David.

  2. #2
    Join Date
    Feb 2010
    Posts
    28

    Default

    Hi,
    if your input is mysql then research SUM(IF()).
    Dirk

  3. #3
    Join Date
    Nov 1999
    Posts
    9,688

    Default

    Use Mondrian
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Yes! Mondrian is great for pivot tables.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5

    Default

    Can somebody help to convert oracle pivot function to mondrian or de-normalizer ?
    I have this
    00501 0149 00119
    00501 0242 00223
    00501 0562 00288
    and want to convert to this
    0501 0149 00119 0242 00223 0562 00288

    Thanks

  6. #6

    Default

    I figured out my solution using de-normalizer.

    Thanks

  7. #7
    Join Date
    Nov 1999
    Posts
    9,688

    Default

    Since the number of columns is fixed, it's not strictly pivoting. I know that in other ETL tools they call de-normalization pivoting but I don't think it actually is that because you have an unknown amount of output columns depending on the distinct dimension values that end up on the X-axis.
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •