Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Transform One Row to N Rows

  1. #1
    Join Date
    Nov 2008
    Posts
    6

    Default Transform One Row to N Rows

    I am currently evaluating Pentaho for a few different client implementations.

    Most of these implementations will feed excel spreadsheets into an ETL process. Most of this data is financial data with the following columns.

    PRIMARY_KEY, Q1, Q2, Q3, Q4

    I would like to translate a single row into 4 insert statements like this where PRIMARY_KEY and QUARTER (integer 1-4) constitute a composite key and revenue is the value from Q1, Q2, Q3, Q4.

    PRIMARY_KEY, QUARTER, REVENUE
    PRIMARY_KEY, QUARTER, REVENUE
    PRIMARY_KEY, QUARTER, REVENUE
    PRIMARY_KEY, QUARTER, REVENUE

    Can anyone tell me what the best way to approach this is? If you could speak in terms of Spoon Transformation Objects that would help.

    Thanks,

    jp4

  2. #2
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    I believe you're looking for the "Row Normaliser" step. I'm attaching an example (hoping I correctly understood your scenario).

    Cheers!
    Last edited by tdidomenico; 11-13-2008 at 02:28 PM.

  3. #3
    Join Date
    Nov 2008
    Posts
    6

    Default

    That's close to what I am looking for. Just a few questions.

    1. Can you use composite keys? My description was simplified.

    If My input looks like this

    PK_COL1, PK_COL2, Q1, Q2, Q3, Q4
    1, 1, 50, 25, 50, 24
    1, 2, 33, 44, 44, 33

    I would like to see this

    PK_COL1, PK_COL2, QUARTER, REVENUE
    1, 1, 1, 50
    1, 1, 2, 25
    1, 1, 3, 50
    1, 1, 4, 25
    1, 2, 1, 33
    1, 2, 2, 44
    1, 2, 3, 44
    1, 2, 4, 33


    Thanks,

    jp4

  4. #4
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Sure, it's the same thing. I've updated the attachment.
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2008
    Posts
    6

    Default

    Can you please explain how PK is mapped to PK1 and PK2? I don't see that explicitly done anywhere.

    Thanks,

    jp4

  6. #6
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    You can find everything neatly explained here:

    http://wiki.pentaho.com/display/EAI/Row+Normalizer

    Cheers!

  7. #7
    DEinspanjer Guest

    Default

    There is a Row Normalizer example in the samples directory, but it is a little funky.
    I'm attaching one that follows your use case pretty closely.

    I think that tdidomenico probably meant to enter "quarter" instead of "PK" as the typename field which is why you were getting confused about that.

    Take a look at this. If it works as you'd expect, I think I'll add it to the samples directory.
    Attached Files Attached Files
    Last edited by DEinspanjer; 11-13-2008 at 02:47 PM. Reason: used wrong name. someone already linked to guide.

  8. #8
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Oops. Thanks Daniel. Shame on me.

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.