Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Data Transformation Question

  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Default Data Transformation Question

    Hi all,

    I am attempting my first transformation with Spoon, and I'm running into a bit of a problem. I have a CSV file input that has a "header" for groups of information, as so:

    Pennsylvania
    Philadephia,17601
    Lancaster,17603

    Florida
    Orlando,07410
    Naples,03456

    I'd like to basically prepend the state to each associated row and come out with rows consisting of the following that I can apply more transformations to:

    Pennsylvania,Philadelphia,17601
    Pennsylvania,Lancaster,17603
    .
    .
    .
    Florida,Naples,03456

    Due to the parallel nature of Pentaho, I'm really having a problem doing this - Do I need some sort of preprocessing, or is it possible to do this in Pentaho and I just don't know how?

    Thank you very much in advance for your help.

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    You are correct about the parallel processing. If you separate the rows with a Filter or Switch/Case you have to be careful about the order when you put them back together.

    For similar situations, I have used the Javascript step to "latch" values for future rows. It would go something like this:

    Code:
    // Assuming City and Zip are the two incoming fields...
    var State, current_state;
    if (current_state == null) {
        current_state = "Unknown State";
    }
    if (Zip == null) {                // It's a State row so steal the state name from the City field and save it.
        State = City;
        City = null;
        current_state = State;
    } else {                             // It's a City row so recall the last known State.
        State = current_state;
    }
    // Add the State field to the outgoing rows.
    After this step, discard any rows in which City is null.
    Last edited by darrell.nelson; 10-05-2011 at 09:10 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

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.