Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Check for non working days

  1. #1

    Default Check for non working days


    It looks simple but I can't get it solved.

    My problem is that I have rows with transaction data including a Start and End day. Now I want to check them against a weekend/holiday table to identify how many non-working days are in the period.

    Transactional table:

    START ; END ; Name
    20090821 ; 20090824 ; R1

    Non-working day table:

    Result should look like:
    START ; END ; Name ; NonWorking ; Working
    20090821 ; 20090824 ; R1 ; 2; 2

    I tried it with a cartesian join but failed. Thought this is a common problem but couldn't find anything.

    Anyone an idea how to solve this?

    Last edited by markbe; 08-26-2009 at 06:55 AM.

  2. #2

    Default Loop via javascript

    i was in a simular situation, just with a number range.
    i have rows with ranges and need to lookup each number in the range.

    i solved it with using a loop in a javascript step which creates new rows,
    this could be working for you too:

    - create a loop in javascript step for each day with a new column for the day
    - then create a new row in the loop
    - don't forward the orignal input-row after the loop

    then a Lookup-Step
    - lookup the day, return 1 when found, otherwise 0, as non-workday-flag

    then a calculator step
    - workday-flag = 1 - non-workday-flag

    then a grouping step
    - group by the original key and set sum(workday-flag), sum(non-workday-flag) as group results

    if you don't have a key for grouping, add one artificial key via "add sequence" step and use that one

    i hope it helps :-)

  3. #3


    Thank you very much, I think technically this will work. But I think I have to find an other solution unfortunately.

    My table with the data is 140.000 rows the date difference in a row is from1 day (good) to 7 month (very bad) some extreme with 3 years.
    The table with the dates to compare with is 800 rows.

    Looping the rows will burn my notebook I guess ;-)

    Looks like this is not as simple as I thought at the beginning.

  4. #4
    Join Date
    Sep 2007


    you have a ktr that can be a starting point for your ktr

    After the filter, add a DB Lookup against your NOWORKDAY table to set a workday-flag. Default: workday, if found: non_workday.

    Then change the grouping accordingly to your requirements,

  5. #5


    Earlier, I wrote a Javascript to determine how many weekends there are in a certain period. Perhaps you can adapt the script to suit your need.

    Nr_of_days and startdate is input.

    PHP Code:
    var saturday;

    counter 0;
    saturday 0;
    sunday 0;
    nr_of_weekends 0;

     while (
    counter nr_of_days) {
            if (
    getDayNumber(dateAdd(startdate"d"counter),"w") == 7) { saturday 1; }
            if (
    getDayNumber(dateAdd(startdate"d"counter),"w") == 1) { sunday 1; }
            if (
    saturday == && sunday == 1) {
    nr_of_weekends nr_of_weekends 1;
    saturday 0;
    sunday 0;
    counter counter 1

    P.S. If somebody knows a more efficient way, I'm always interested in learning!
    Last edited by rickonline; 08-27-2009 at 06:11 AM.

  6. #6


    Thanks! I will wok on this as it will greatly reduce the number of lookups.


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.