Calculate working days between to dates during load

[#1]

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

[Anonymous]

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

[#3]

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

[Anonymous · Accepted solution]

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))

[#5]

Tks a lot Michael,

That was really helpfull.

Best Regardas,

Luis

[sspringer]

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?

[Anonymous]

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)