Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Least of Timestamps in Columns

  1. #1

    Default Least of Timestamps in Columns

    I have data where each row has 3 timestamps, kind of like:

    Key1,Timestamp1,Timestamp2,Timestamp3
    Key2,Timestamp1,Timestamp2,Timestamp3
    Key3,Timestamp1,Timestamp2,Timestamp3

    I'm looking for the minimum value of each timestamp for each row, lets say:

    Key1,Timestamp2
    Key2,Timestamp3
    Key3,Timestamp3

    This is easy in PostgreSQL (for those of you familiar with) using the LEAST([Arg1],[Arg2],...) function.

    How can I implement this using Pentaho Data Integration? I don't believe I can use the Calculator or Formula step, and I must use the Script step to write a custom script. However, I just want to make sure I'm not missing out on anything.

    Thanks!

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

    Default

    I would use Row Normalizer to convert:

    KeyID, TS1, TS2, TS3
    Key1,Timestamp1,Timestamp2,Timestamp3
    Key2,Timestamp1,Timestamp2,Timestamp3
    Key3,Timestamp1,Timestamp2,Timestamp3

    Into:
    KeyID | TSNum | TSValue
    Key1 | 1 | Timestamp1
    Key1 | 2 | Timestamp2
    Key1 | 3 | Timestamp3
    Key2 | 1 | Timestamp1
    Key2 | 2 | Timestamp2
    Key2 | 3 | Timestamp3
    Key3 | 1 | Timestamp1
    Key3 | 2 | Timestamp2
    Key3 | 3 | Timestamp3

    Now finding the minimum becomes a trivial task with GroupBy step.

  3. #3

    Default

    Thanks, gutlez. I took your advice and tried it out. The unfortunate thing is that PDI has some conversion issues when I do a group by with an aggregate MIN of timestamps. I had to convert it to type Date and then do it, but it worked! Thanks again for the advice.

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

    Default

    Quote Originally Posted by dmukherji View Post
    PDI has some conversion issues when I do a group by with an aggregate MIN of timestamps.
    If they are repeatable, and can be replicated with a DataGrid producing TimeStamps, then post it to http://jira.pentaho.com (Include your repeatable datagrid transformation as a test-case)

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.