Search code examples
timeetlaggregationpentahokettle

how to concat strings on PDI?

I have a PDI (Kettle) job that populates a dim_time table with the columns hour, minute and second.

I'd like to create a fourth (aggregated) column that contains a representation of the given time like HH:MM:SS, how can I do that on PDI?

Solution

  • There are plenty of ways of doing that. If you want this fourth column as a string you can go for this "point-and-click only" solution:

    Insert a Calculator step and insert fields as follow:

    • New Field: h1
    • Calculation: Create a copy of field A
    • Field A: hour
    • Field B:
    • Value Type: String
    • Remove: Y
    • Conversion Mask: 00:
    • New Field: m1
    • Calculation: Create a copy of field A
    • Field A: minute
    • Field B:
    • Value Type: String
    • Remove: Y
    • Conversion Mask: 00:
    • New Field: s1
    • Calculation: Create a copy of field A
    • Field A: second
    • Field B:
    • Value Type: String
    • Remove: Y
    • Conversion Mask: 00 (Attention here, there's no ":")
    • New Field: t1
    • Calculation: A + B
    • Field A: h1
    • Field B: m1
    • Value Type: String
    • Remove: Y
    • Conversion Mask:
    • New Field: time_string
    • Calculation: A + B
    • Field A: t1
    • Field B: s1
    • Value Type: String
    • Remove: N (Attention here, choose N otherwise your desired field won't be added to stream)
    • Conversion Mask:

    And that should be all you need. Probably there are methods using javascript, but you'll need to code and find the right string formatting functions.