Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Add Calculated Fields to stream

  1. #1
    Join Date
    Oct 2015
    Posts
    10

    Default Add Calculated Fields to stream

    Hi folks!

    I'm newbie in PDI. I have to calculate additional field based on existing one in Input Stream and add it to the Output Table.
    Specifically:
    I have a date field in the input file like following: "07/09/2014" (MM/DD/YYYY) and need to translate it to an integer date ID field like YYYMMDD.
    I've added a new field to the DB table but don't know how to add it to the pipeline.

    Thanks in advance,
    Sveta

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Storing date values as strings in a database is a bad habit I see way too often.
    Anyways, you can convert the field data type to String using a field format string like yyyyMMdd in a Select-Values (tab Meta-Data) step if autoconversion is not applicable.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jun 2014
    Posts
    1

    Default

    Accept the date (MM/DD/YYYY),
    1st convert it to String format using calculator step [value type -> string and conversion mask -> yyyyMMdd] name as date_st
    2nd convert date_st to integer using same calculator step [value type -> integer] name as date_new
    date_new is a integer converted date(yyyyMMdd).

  4. #4
    Join Date
    Oct 2015
    Posts
    10

    Default

    Hi,
    Thanks @Benakesh and @marabu for your comments!
    Actually I found a way to "translate" a date to YYYYMMDD int: retrieved in Calculator date parts into 3 integer fields (tdYear, tdMonth and tdDate) and used Formula to combine them into integer field - TDID.
    BUT - I couldn't add this value as additional field to Table Output (TDID was added to Fields map). The transformation failed with message like "TDID is required but not found" .
    Interesting that I can see the output value if i connect Formula to separate Text File Output.
    So my question is: how to add fields calculated in parallel stream to single Output? Should I use one of Join transformations?
    Sorry for being unclear! :-)

    BTW, @marabu - from my experience with DWH applications it's not a bad practice to keep tradeDateId integer field to use a quick DB Sort, Join, etc. for reports.
    Thanks,
    Sveta

  5. #5
    Join Date
    Oct 2015
    Posts
    10

    Default

    Well, I've found a solution: added 2 Sort Rows transformations to both main stream and calculation one and Merge Join to combine two flows.
    Then Table Output successfully got additional calculated field.

    Hope it will help somebody.

    Regards,
    Sveta

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.