Etl Field Transform DateDuration help

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Etl Field Transform DateDuration help

      Good day,

      I try to find a way how to work with the Date Duration Function in ETL-Manager: The goal will be to see the duration in Minutes between open and closed

      I have different fields

      open day = 01.07.2014
      close day = 02.07.2014
      open time = 11:58
      close time = 13:44

      I try now to get the difference between open and close. When I understand it correct I can use DateDuration for this. But for any reson it does not work.

      I always get the failure:



      Failed to transform value 01.07.2014 of column my Open Date day in function Duration_day_open-closed_formatted: Unparseable date: "01.07.2014"


      When I try to change the Data to a format like 01072014 I get the same error.




      next I tried to combine the fields open and close with concatenation but if I do this it looks like 01.07201411:58
      and when I try to store the Data then I get a BIG Java error


      any Idea how I can get this sorted?




      P.S. Still using Palo 5.1 not 5.1 SR1

      The post was edited 1 time, last by jensb ().

    • imho a Groovy function using SimpleDateFormat is better cause date parsing, duration calculation and formatting can be performed all in one function!

      well I'm fond of it, don't pay attention :)

      still, DateDuration will not work if the format of both dates are different (cause you can only enter 1 source format)
      laloune

      Post hoc, non est propter hoc

    • Hi,
      sorry for my late reply was on vacation and now I am back :)

      I receive the Data from an SQL output.

      Let me try to explain what I do.

      I have 4 fields out of sql.

      field 1: open date: 16.04.2014
      field 2: open time: 22:45
      field 3: close date: 17.04.2014
      field 4: close time: 03:15

      first what I do is a concatenation of field 1&2 and field 3&4

      so it looks at the end like this:

      field 5: open date & time: 16.04.2014 22:45
      field 6: close date& time: 17.04.2014 03:15

      what I need is the formular in groovy which can show me the differnce between field 6 and 5. so as a result : 270


      I tried:

      return _date1 - _date2;


      but this does not work

    • Thanks a lot.
      I understand the problem now. But to be honest I have no Idea how I can change now the code to pickup fields as every field has different open and close date´s and times.
      I copied a screenshot in. Maybe this help?
      Images

      • Capture.JPG

        64.19 kB, 806×423, viewed 431 times

    • Should be something like that I guess (_input1 and _input2 refer to to your source columns):

      Java Source Code

      1. import java.text.SimpleDateFormat;
      2. import groovy.time.TimeCategory;
      4. inputFormat=new SimpleDateFormat('dd.MM.yyyy H:mm');
      6. inputOpen=_input1;
      7. inputEnd=_input2;
      9. inputOpenAsDate=inputFormat.parse(inputOpen);
      10. inputEndAsDate=inputFormat.parse(inputEnd);
      12. diff=TimeCategory.minus(inputEndAsDate, inputOpenAsDate);
      14. return diff;
      Display All

    • right but I don't get your screenshot : your input type is integer, here I would expect to find a String (that is gonna be parsed using <format>.parse();

      If I understand well you have 4 input fields

      open date
      open time
      close date
      close time

      then why don't you just concatenate both with a space to get the right format to parse them ?

      if you just need the minutes, why don't you just use diff.minutes ? Do you have an example with the dates where this should not work as wished ?
      laloune

      Post hoc, non est propter hoc