topic Calculate working days between to dates during load in QlikView

Calculate working days between to dates during load

Thu, 31 Mar 2011 18:21:49 GMT

Hi,

I'm a newbie in qlikview and i've this scenario:

I' ve a table with to dates: enddate and startdate; I need to calculate the number of working days between the two during load and add a new field with it, how can I do it?

I need to check each date between startdate and enddate to see if it is a working day or a holiday. I tried to use a routine, but I can´t get i to work inside load statement. The holidays are in another table.

Tks,

Luis

Calculate working days between to dates during load

Anonymous — Thu, 31 Mar 2011 19:07:37 GMT

Luis,
See networkdays() function.
I never tried it in script, but expect it to work.

Calculate working days between to dates during load

Fri, 01 Apr 2011 09:57:30 GMT

Hi Michael,

Your tip is almost what I need, but the problem is that I need this to be generic, that is I've the holidays in a table and I need to use this dates. I've seen the networkdays() function and for it to work I need to pass as arguments the holidays. How can I check it agaisnt the days in the holidays table?

My best Regards,

Luis

Calculate working days between to dates during load

Anonymous — Fri, 01 Apr 2011 16:50:40 GMT

Luis,

If you have a list in separate table, you can concatenate it in a variable, e.g.
tmp:
LOAD
concat(distinct Holiday, ',') as Holidays
RESIDENT HolidayList;
LET vHolidayList = peek('Holidays');
DROP TABLE tmp;

And, the expression will be:
networkdays(startdate, enddate, $(vHolidayList))

Calculate working days between to dates during load

Mon, 04 Apr 2011 10:29:46 GMT

Tks a lot Michael,

That was really helpfull.

Best Regardas,

Luis

Calculate working days between to dates during load

sspringer — Thu, 19 Apr 2012 20:08:58 GMT

When I used your formula above I get a vHolidayList such as: 1/1/2009, 1/1/2010...

When I believe to work in the equation it needs to be '1/1/2009', '1/1/2010',...(with the ' signs around each date indicating text)?

What am I doing wrong?

Calculate working days between to dates during load

Anonymous — Fri, 20 Apr 2012 00:28:41 GMT

Here is how you can create the single quotes - in the beginning, in the end, and between the values:

chr (39) & concat(distinct Holiday, chr(39) & ' ,' & chr(39) ) & chr(39)