Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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;
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,
Hi swuehl,
Sorry for the late reponse. Thanks for your help!!! It works now.
Regards
iSam
Hi Sunil Chauhan,
Thanks for your help!!!!
Regards,
iSam