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

    Hello,

    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:
    NOWORKDAY
    20090822
    20090823

    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?

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

  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:

    Javascript-Step:
    - 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

    Default

    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.
    Regards
    Mark

  4. #4
    Join Date
    Sep 2007
    Posts
    834

    Default

    Here:
    http://forums.pentaho.org/showthread.php?t=71563
    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,
    mc

  5. #5

    Default

    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;
    var 
    sunday;
    var 
    counter;
    var 
    nr_of_weekends;

    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

    Default

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

    Regards
    Mark

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.