ETL Datumsdifferenz berechnen ohne Wochenende

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

    • ETL Datumsdifferenz berechnen ohne Wochenende

      Hallo Community,

      ich habe eine Tabelle im ETL die unter anderem 2 Datumsspalten hat. Ich würde nun gerne die Differenz zwischen beiden Spalten berechnen, allerdings ohne die Wochenenden. Feiertage sind in diesem Fall egal.
      In Excel wurde das bisher über die Nettoarbeitstage Formel minus 1 gelöst.

      Hier noch ein kurzes Beispiel was gerechnet werden soll
      Startdatum: 18.07.2014 Enddatum: 25.07.2014 Ergebnis: 5 (Differenz = 7 minus 2 Tage Wochenende).

      Falls jemand dafür ein Codebeispiel hätte, wäre ich sehr dankbar. Löst man soetwas mit Groovy?

      Viele Grüße,

      Stephan Wetzelsberger

    • import java.text.SimpleDateFormat;

      Hi Stepha,

      let's try this groovy script:


      Source Code

      1. def endDate=new Date();
      2. def startDate='25.07.2014';
      4. SimpleDateFormat inputFormat=new SimpleDateFormat('dd.MM.yyyy');
      5. SimpleDateFormat weekendFormat=new SimpleDateFormat('E');
      7. startDateAsDate=inputFormat.parse(startDate);
      9. nbrOfDays=0;
      10. while (startDateAsDate<=endDate)
      11. {
      12. isNotWeekEnd=(weekendFormat.format(startDateAsDate) in ['Sa','So'])?0:1; // the list should be adapted according to the groovy locale, here it is german
      13. nbrOfDays=nbrOfDays + isNotWeekEnd;
      14. startDateAsDate++;
      15. }
      17. println nbrOfDays;
      Display All


      you can execute it in the groovy console (to be downloaded here: groovy.codehaus.org/Download)

      or try to adapt it directly in the ETL transform.

      Maybe there is a simpler solution using a Calendar object but I am not very familiar with it

      hope this helps !
      laloune

      Post hoc, non est propter hoc

    • Hi laloune,

      thank you very much for the script.
      Unfortunately my groovy skills are not good enough to adapt that into my ETL example.

      Assuming I have two date columns "column1" and "column2" that I use as an input in a fiel transform and I would like to have the number of days (difference) as a result.
      How to change the source code? And what data type would you recommend if you don't have "Date" as datatype available?

      Thanks for your help,

      Stephan

    • okay, let's try this:


      Source Code

      1. def endDate=_input1; // put "column1" as your first input field
      2. def startDate=_inpu2; // put "column2" as your second input field
      4. SimpleDateFormat inputFormat=new SimpleDateFormat('dd.MM.yyyy'); // adapt the format accordingly, for instance if your date format is 11/17/2014 then put "MM/dd/yyyy"
      5. SimpleDateFormat weekendFormat=new SimpleDateFormat('E');
      7. startDateAsDate=inputFormat.parse(startDate);
      8. endDateAsDate=inputFormat.parse(endDate);
      10. nbrOfDays=0;
      11. while (startDateAsDate<=endDateAsDate)
      12. {
      13. isNotWeekEnd=(weekendFormat.format(startDateAsDate) in ['Sa','So'])?0:1; // the list should be adapted according to the groovy locale, here it is german
      14. nbrOfDays=nbrOfDays + isNotWeekEnd;
      15. startDateAsDate++;
      16. }
      18. return nbrOfDays;
      Display All


      hope this helps !
      laloune

      Post hoc, non est propter hoc

    • Warning: Failure in function

      Hello,

      after trying the groovy script, my result column is empty.
      The following Information Dialog appears:

      Warning:Failure in function test: startup failed: Script108.groovy: 4: unable to resolve class SimpleDateFormat @ line 4, column 18. SimpleDateFormat inputFormat=new SimpleDateFormat('dd.MM.yyyy'); ^
      Script108.groovy: 4.....

      Any ideas of what went wrong?

      Thank you,

      Stephan