topic Re: Counting working days between dates in QlikView

Counting working days between dates

Anonymous — Mon, 26 May 2014 08:29:10 GMT

Hi all,

Table A is my fact table with orders:

Order	OrderDate	ShipmentDate
A	2013-02-07	2013-02-12
B	2013-12-30	2014-01-03

Table B is my date table:

Date	WorkingDay
2013-02-08	1
2013-02-09	0
2013-02-10	0
2013-02-11	1
2013-02-12	1
.....	.....
2013-12-31	0
2014-01-01	0
2014-01-02	1
2014-01-03	1

I would like to count the shipment working days. The correct number of working days should be 3 for order A... and 2 for order B as New years eve and New years day is not working days.

How can I count this? I've tried the NewWorkDays() function, but it doesn't handle our holidays. I've also tried the IntervalMatch() function without proceeding.

Please help me.

Best regards,

Filip

Re: Counting working days between dates

tresB — Mon, 26 May 2014 08:33:15 GMT

Networkdays() has a third parameter which accepts your list of holidays. Have you tried that?

Re: Counting working days between dates

Mon, 26 May 2014 08:35:05 GMT

You may do IntervalMatch or your own intersection table using while clause in load script.

Re: Counting working days between dates

Anonymous — Mon, 26 May 2014 08:46:58 GMT

OK, how do I do that?

The best of all would be if I could get the sum of working days as a new column in table A.

Re: Counting working days between dates

Mon, 26 May 2014 09:17:59 GMT

Filip,

you may try script like this:

tbl1:

LOAD Autonumber(Num(OrderDate) & '|' & Num(ShipmentDate)) as IntervalID,*, ShipmentDate-OrderDate as initialDNr INLINE [

Order, OrderDate, ShipmentDate

A,2013-02-07,2013-02-12

B,2013-12-30,2014-01-03

];

wd:

LOAD * INLINE [

Date,Wd

2013-02-08,1

2013-02-09,0

2013-02-10,0

2013-02-11,1

2013-02-12,1

2013-12-31,0

2014-01-01,0

2014-01-02,1

2014-01-03,1

];

wd_tmp:

load Date as NonWorkDate Resident wd where Wd=0;

bridge:

IntervalMatch (NonWorkDate) Load distinct OrderDate, ShipmentDate resident tbl1;

Join (wd_tmp)

Load NonWorkDate, Autonumber(Num(OrderDate) & '|' & Num(ShipmentDate)) as IntervalID

Resident bridge;

join (tbl1)

load IntervalID, count(NonWorkDate) as nwd_nbr Resident wd_tmp Group by IntervalID;

table:

load Order, OrderDate, ShipmentDate, initialDNr-nwd_nbr as wd_nbr Resident tbl1;

DROP Tables bridge,wd_tmp,tbl1;

Re: Counting working days between dates

Mon, 26 May 2014 10:09:04 GMT

Hi, Filip!

You can use this code:

Orders:

LOAD * INLINE [

Order, OrderDate, ShipmentDate

A, 2013-02-07, 2013-02-12

B, 2013-12-30, 2014-01-03

];

WorkDays:

LOAD * INLINE [

Date, WorkingDay

2013-02-08, 1

2013-02-09, 0

2013-02-10, 0

2013-02-11, 1

2013-02-12, 1

2013-12-31, 0

2014-01-01, 0

2014-01-02, 1

2014-01-03, 1

];

temp:

IntervalMatch(Date) LOAD OrderDate,ShipmentDate Resident Orders;

Left Join(temp)

LOAD * Resident Orders;

Left Join(temp)

LOAD * Resident WorkDays;

DROP Tables Orders,WorkDays;

orders_work_days:

LOAD Order, OrderDate, ShipmentDate, Sum(WorkingDay) as WorkongDays

Resident temp

Group By Order, OrderDate, ShipmentDate;

DROP Table temp;

Re: Counting working days between dates

Anonymous — Tue, 27 May 2014 08:19:55 GMT

Thank you very much, this works great!

Can you please explain the stages with the bridge, that I didn't understand (though it worked)...

Re: Counting working days between dates

Anonymous — Tue, 27 May 2014 08:21:47 GMT

Thanks for your post, this works well if you only have order headers... My example was a bit simplified, when assigning this to my real data with order rows, I got duplicate values on each row using the sum() function. Dariusz example above works perfect with rows as well!