Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aboumejjane
Contributor

Integrating Networkdays in MasterCalander

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

1 Solution

Accepted Solutions
MVP
MVP

Integrating Networkdays in MasterCalander

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,

4 Replies
chauhans85
Esteemed Contributor

Integrating Networkdays in MasterCalander

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;

MVP
MVP

Integrating Networkdays in MasterCalander

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,

aboumejjane
Contributor

Integrating Networkdays in MasterCalander

Hi swuehl,

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

Regards

iSam

aboumejjane
Contributor

Integrating Networkdays in MasterCalander

Hi Sunil Chauhan,

Thanks for your help!!!!

Regards,

iSam


Community Browser