6 Replies Latest reply: Apr 19, 2012 8:28 PM by Michael Solomovich

# Calculate working days between to dates during load

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

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

• ###### Calculate working days between to dates during load

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

Luis,

If you have a list in separate table, you can concatenate it in a variable, e.g.
tmp:
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

Tks a lot Michael,

Best Regardas,

Luis

• ###### Calculate working days between to dates during load

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

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)