topic Integrating Networkdays in MasterCalander in QlikView

Integrating Networkdays in MasterCalander

Anonymous — Thu, 25 Aug 2011 15:35:45 GMT

Hi Guys,

I'm quite new to qlikview so forgive me for my ignorance.

I have created a MasterCalander and added the Networkdays function in it. By doing this I would like the weekends to automatically be removed. However I don't know if this is the right approch. The main goal is to automatically remove all the weekends between the departure date (DEP) field en arrival date (ARR) field.

I tried something myself.

This is my script.

LET varMin = num('01-01-2011');

LET varMax = num('31-12-2011');

LET vWorkDays = NetWorkDays('01-01-2011', '31-12-2011'); //does not work

Date:

Load Date($(varMin), ($(vWorkDays)) + rowno() -1) as tempdate

AutoGenerate ($(varMax) - $(varMin)) - $(vWorkDays) +1; //does not work

Dates:

LOAD

Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

From

[xyz.csv]

Calender:

LOAD

tempdate as Date,

Year(tempdate) as Year,

Month(tempdate) as Month,

Week(tempdate) as Week,

'Q' & Ceil(Month(tempdate) / 3) as Quarter

Resident Date;

Drop Table Date;

Please help me, I'm stuck

Integrating Networkdays in MasterCalander

SunilChauhan — Thu, 25 Aug 2011 15:56:22 GMT

see the followin

LET varMin = num('01-01-2011');

LET varMax = num('31-12-2011');

LET vWorkDays = NetWorkDays(makedate(2011,01,01), makedate(2011,12,31));

Date:

Load Date($(varMin), ($(vWorkDays)) + rowno() -1) as tempdate

AutoGenerate (varMax - varMin) - vWorkDays +1;

Dates:

LOAD

Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

From

[xyz.csv]

Calender:

LOAD

tempdate as Date,

Year(tempdate) as Year,

Month(tempdate) as Month,

Week(tempdate) as Week,

'Q' & Ceil(Month(tempdate) / 3) as Quarter

Resident Date;

Integrating Networkdays in MasterCalander

swuehl — Thu, 25 Aug 2011 16:19:07 GMT

I am not sure if you want to use networkdays for that - it gives the number of working days between two dates.

I think you are better of using the weekday function, creating a flag for working day / weekand or using the function in a where statement during load.

e,g,

LET varMin = num(Date#('01-01-2011','DD-MM-YYYY'));

LET varMax = num(Date#('31-12-2011','DD-MM-YYYY'));

LET vWorkDays = NetWorkDays(varMin,varMax); //does work now

Date:

Load Date($(varMin) + recno() -1) as tempdate

AutoGenerate ($(varMax) - $(varMin)) - $(vWorkDays) +1; //does work now, but what do you want to achieve?

// I would rather use AutoGenerate ($(varMax) - $(varMin)) +1;

Dates:

LOAD

Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

From

[xyz.csv]

Calender:

LOAD

tempdate as Date,

Year(tempdate) as Year,

Month(tempdate) as Month,

Week(tempdate) as Week,

'Q' & Ceil(Month(tempdate) / 3) as Quarter,

//if(weekday(tempdate)<5,1,0) as workday,

//text(weekday(tempdate)) as Weekday

Resident Date where weekday(tempdate)<5 ;

//this removes the weekend from your master calendar, if you want that? I would rather keep all days and use a flag (see commented line above)

Regards,

Stefan

P.S. I think instead of

Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

you probably want

Date(daystart(Date#(DEP,'DD-MM-YYYY hh:mm')), 'DD-MM-YYYY') as Date,

Integrating Networkdays in MasterCalander

Anonymous — Mon, 29 Aug 2011 09:42:56 GMT

Hi swuehl,

Sorry for the late reponse. Thanks for your help!!! It works now.

Regards

iSam

Integrating Networkdays in MasterCalander

Anonymous — Mon, 29 Aug 2011 09:43:57 GMT

Hi Sunil Chauhan,

Thanks for your help!!!!

Regards,

iSam