topic How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1. in QlikView

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Mon, 01 Nov 2010 18:51:57 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Calculate-dd_2D00_mm_2D00_yyyy-Plus-X-days-_2D00_-2010103002.qvw]

Hi guys,

I Need help to work around the present situation (please see the attachment):

I need to calculate Target Date as StartDate + ExtraDays when IsWorkDay=1.

In example:

If StartDate = 30/09/2010 (see Line No 15)

So, Target Date should be 04/10/2010

LineNo	StartDate	Day	IsWorkDay	ExtraDays
15	30/09/2010	Thu	1	3
16	01/10/2010	Fri	0
17	02/10/2010	Sat	0
18	03/10/2010	Sun	1
19	04/10/2010	Mon	1
20	05/10/2010	Tue	0
21	06/10/2010	Wed	1

Thanks in advance,

Aldo.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Mon, 01 Nov 2010 18:59:12 GMT

I think this:

date(rangesum(StartDate,if(IsWorkDay,ExtraDays))) as TargetDate

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Mon, 01 Nov 2010 20:36:41 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Calculate-dd_2D00_mm_2D00_yyyy-Plus-X-days-_2D00_-2010103003.qvw]

Hi John,

Thanks for your answer.

Almost there, but having some errors, please take a look:

LineNo	StartDate	Day	IsWorkDay	ExtraDays	TargetDate
15	30/09/2010	Thu	1	3	03/10/2010	Should be 04/10/2010	Error = 1
16	01/10/2010	Fri	0		01/10/2010
17	02/10/2010	Sat	0		02/10/2010
18	03/10/2010	Sun	1		03/10/2010
19	04/10/2010	Mon	1		04/10/2010

22	07/10/2010	Thu	1	5	12/10/2010	Should be 14/10/2010	Error = 2
23	08/10/2010	Fri	0		08/10/2010
24	09/10/2010	Sat	0		09/10/2010
25	10/10/2010	Sun	1		10/10/2010
26	11/10/2010	Mon	0		11/10/2010	This is a Holiday…
27	12/10/2010	Tue	1		12/10/2010
28	13/10/2010	Wed	1		13/10/2010
29	14/10/2010	Thu	1		14/10/2010

35	20/10/2010	Wed	1	1	21/10/2010	Ok!
36	21/10/2010	Thu	1		21/10/2010

39	24/10/2010	Sun	1	7	31/10/2010	Should be 01/11/2010	Error = 1
40	25/10/2010	Mon	1		25/10/2010
41	26/10/2010	Tue	1		26/10/2010
42	27/10/2010	Wed	1		27/10/2010
43	28/10/2010	Thu	1		28/10/2010
44	29/10/2010	Fri	0		29/10/2010
45	30/10/2010	Sat	0		30/10/2010
46	31/10/2010	Sun	1		31/10/2010
47	01/11/2010	Mon	1		01/11/2010

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Mon, 01 Nov 2010 21:16:46 GMT

I guess I'm confused. Why is 30/09/2010 + 3 ExtraDays = 04/10/2010? That's 4 days later, not 3 days later. Where does the extra day come from?

Does it have something to do with only adding work days, not just days? No, because Neither 01/10/2010 nor 02/10/2010 are work days, so if I added 3 work days, I'd be at 05/10/2010.

So, no idea what you're doing, then.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Tue, 02 Nov 2010 12:08:52 GMT

Hi John,

Yeap, talking about neto working days, so if...

I start on 30/09/2010 at 08:00 this is my first working day

03/10/2010 will be the second working day,

04/10/2010 will be the third working day

and Target Date would be 05/10/2010.

Anyway, rangesum is not doing anything... ypu can see that Target Date is always StartDate + ExtraDays...

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Tue, 02 Nov 2010 23:30:46 GMT


Aldo wrote:Anyway, rangesum is not doing anything... ypu can see that Target Date is always StartDate + ExtraDays...

Rangesum() is just addition, but treats nulls as 0, while regular addition turns the entire thing null. So rangesum() wasn't intended to skip non-working days. I didn't realize that's what you wanted.

The below does what I think you want, counting ONLY the days you've identified as work days. In practice, you'd want to combine it with earlier loads, but it's simpler to show you something that could be done at the end of your script.

WorkDates:
LOAD DISTINCT StartDate as WorkDate
RESIDENT BaseDates
WHERE IsWorkDay
ORDER BY StartDate
;
LEFT JOIN (BaseDates)
LOAD
LineNo
,if(ExtraDays,date(fieldvalue('WorkDate',fieldindex('WorkDate',StartDate)+ExtraDays)),StartDate) as TargetDate2
RESIDENT BaseDates
;
DROP TABLE WorkDates
;

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Wed, 03 Nov 2010 15:09:43 GMT

That's exactly what I need!

Thanks a lot John!

Aldo.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Wed, 03 Nov 2010 16:24:45 GMT

I see one possible problem. Do you have any rows where you specify ExtraDays, but the StartDate is NOT a work day? If so, the fieldindex() would fail. But I don't know if this actually matters in your real data.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Wed, 03 Nov 2010 16:38:47 GMT

Yes, you are right, but I am taking into consideration that StartDate must be a Working day.

Anyway,is there any work around to solve the problem? There is always some intelligence one that does exactly the opposite he should do...

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Wed, 03 Nov 2010 17:55:46 GMT

Well, rather than fieldindex(), we could manually set a working date sequence to get that number, and set it on non working days without incrementing it. Again, you can probably combine some of this with earlier loads, but tacking it on the end, you can do this:

WorkDates:
LOAD DISTINCT StartDate as WorkDate
RESIDENT BaseDates
WHERE IsWorkDay
ORDER BY StartDate
;
LEFT JOIN (BaseDates)
LOAD
LineNo
,WorkDateSequence
,if(ExtraDays,date(fieldvalue('WorkDate',WorkDateSequence+ExtraDays)),StartDate) as TargetDate2
;
LOAD
LineNo
,StartDate
,ExtraDays
,rangesum(peek(WorkDateSequence),IsWorkDay) as WorkDateSequence
RESIDENT BaseDates
ORDER BY StartDate
;
DROP TABLE WorkDates
;

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Sat, 06 Nov 2010 07:18:19 GMT

Hi John,

I tried the second script, but it works with errors. I believe is related to the work date secuence. I am trying to fix the script, but I don't understand all the details.

What's the meaning of "LEFT JOIN (BaseDates)", and since there is no Resident /From, where is getting the data from?

The table below, is joining to Which table?

LOAD

LineNo

,StartDate

,ExtraDays

,rangesum(peek(WorkDateSequence),IsWorkDay) as WorkDateSequence

RESIDENT BaseDates

ORDER BY StartDate;

Thanks again,

Aldo.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

johnw — Mon, 08 Nov 2010 19:26:34 GMT

Ah, your table is BaseDates1 instead of BaseDates. So just change all my BaseDates to BaseDates1. Then it works.

When one load with no resident/from comes before another, it's called a preceeding load. The source of the first load is the second load. It is loading from another load. That also answers your other question, which is that the load you copied in your post isn't going directly into ANY table, it's just the source for the load above it.

How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

Mon, 08 Nov 2010 19:39:33 GMT

Yeap, it works!

Thanks again,

Aldo.