8 Replies Latest reply: Aug 5, 2011 9:40 AM by wanderson.ferro RSS

    Using two resident tables

      Helo,

       

       

      I have one problem in using resident tables in my script.

       

      The question is:

      • I have 2 tables (both residents):

       

        • The first with an Id, a start date and a end date.
        • The second with nonworking days (weekends and hollidays)

       

       

       

       

      • I need to generate a third table, with the Id, the start date, the end date, the number of calendar days between the dates, and the number of working days between the dates.
      • The formula for the number of calendar days is:
        • [End date] - [start date]. This I calculated directly in the script.

       

       

      • The formula for the number of workind days is:
        • [End date] - [start date] - count(if([nonworking day] >= [start date] and [nonworking day] <= [end date], [nonworking day])).

       

       

      Somebody could show me how to create a script to create the third table?

       

      Thanks.

        • Re: Using two resident tables

          wanderson.ferro wrote:

           


          • The formula for the number of workind days is:
            • [End date] - [start date] - count(if([nonworking day] >= [start date] and [nonworking day] <= [end date], [nonworking day])).

           

          networkdays ( start:date, end_date {, holiday} )

          Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

          Examples:

          networkdays ('2007-02-19', '2007-03-01') returns 9 

          networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8 

           

          See attachment

            • Using two resident tables

              But, can I use this function in the same load script?

              My Start Date and End Date are in one table, but the holidays are in other table.

              My load is not working because the field holyday, ou nonworkingday is in other table.

               

              Is it possible to resolve it in the same load script?

                • Using two resident tables
                  Rob Wunderlich

                  See the example provided by Nick. He builds the holiday list in a variable using concat(). The holidays can be in another table.

                   

                  -Rob

                    • Using two resident tables

                      Yes, the example provided by nick calculate de working days in an object (graphic).

                       

                      I need to load the working days calculated like a field in the load script.

                       

                      In my example, my model would have just one table, with the dates (start and end) and the number of working days between them.

                       

                      Looking the script, he just charge the data and after calculate the working days in the graphic.

                       

                      Is it possible to work with 2 tables in the load script?

                        • Using two resident tables

                          I've showed two approaches in my example to calculate working days. It's up to you which one you will implement in your script.

                            • Re: Using two resident tables

                              Nick, thanks for your examples, but I think they don´t solve my problem.

                               

                              You calculate the working days by 2 different ways:

                                   - Using the function networkdays;

                                   - Using a combination of the function sum.

                               

                              Both ways are calculated in the CH01 object.

                               

                              But, I need the working days are already calculated at the end of data load.

                              In your examples, the calculation occurs dynamically in the object(CH01).

                               

                               

                              If your examples have resolved the issue, I apologize. I could not able to find the solution.

                               

                                • Re: Using two resident tables

                                  It's easy. All what you need is just to convert expressions from the chart

                                   

                                   

                                  sum(DayCount)
                                  sum(DayCount) - sum({<HolydayFlag={1}>} DayCount)
                                  

                                   

                                  to a script at the end of my existing example script.

                                   

                                   

                                  Left Join (DateRangeCalendar)
                                  LOAD
                                      dmDate,
                                      HolydayFlag,
                                      DayCount
                                  Resident Calendar;
                                  
                                  Left Join (DateRange)
                                  LOAD
                                      StartDate,
                                      EndDate,
                                      CalendarDays,
                                      Holydays,
                                      CalendarDays - Holydays as WorkDays
                                  ;
                                  LOAD
                                      StartDate,
                                      EndDate,
                                      Sum(DayCount) as CalendarDays,
                                      Sum(HolydayFlag) as Holydays
                                  Resident DateRangeCalendar
                                  Group by StartDate,
                                      EndDate;
                                  
                                  DROP Tables DateRangeCalendar, Calendar;