topic Using two resident tables in QlikView

Using two resident tables

Thu, 04 Aug 2011 15:15:13 GMT

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

Thu, 04 Aug 2011 15:18:36 GMT

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

Thu, 04 Aug 2011 17:00:44 GMT

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

rwunderlich — Thu, 04 Aug 2011 19:36:56 GMT

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

Thu, 04 Aug 2011 19:56:33 GMT

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

Thu, 04 Aug 2011 23:26:13 GMT

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

Fri, 05 Aug 2011 11:41:31 GMT

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

Fri, 05 Aug 2011 13:32:27 GMT

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;

Re: Using two resident tables

Fri, 05 Aug 2011 13:40:59 GMT

Nick, thanks a lot!

I was not able to find this way to write the script by myself.

I´m just starting to work with QlikView.

Thank again!