topic Re: Date according Business Days in QlikView

Date according Business Days

rakeshkumar1890 — Fri, 15 Nov 2024 23:48:36 GMT

Hello Everyone,

Need your help to find out Start date:

Table:

OrderLine	Parts	CleanedDate	PlanDate	OutputDate
57120157	870108	8/7/2020	7/22/2020
57120157	870108	8/7/2020	7/23/2020
57120157	870108	8/7/2020	7/24/2020
57120157	870108	8/7/2020	8/3/2020
57120157	870108	8/7/2020	8/4/2020
57120157	P11888	8/7/2020	7/29/2020
57120157	P11888	8/7/2020	7/30/2020
57120157	P11888	8/7/2020	7/31/2020
CWET157	979797	8/3/2020	8/5/2020	8/5/2020
CWET157	979797	8/3/2020	8/9/2020	8/9/2020
CWET157	979797	8/3/2020	8/10/2020	8/10/2020
CWET157	YTR789	8/4/2020	8/5/2020	8/9/2020
CWET157	YTR789	8/4/2020	8/9/2020	8/9/2020
CWET157	YTR789	8/4/2020	8/12/2020	8/12/2020

Here in my raw data - there are two date fields (CleanedDate and PlanDate). I Need output field with given below scenorios.

1.Need to check PlanDate to CleanedDate + 2 Business days (function - Networkdays (Monday to Friday)) ,

In below case

OrderLine

Parts

CleanedDate

PlanDate

OutputDate

57120157	870108	8/7/2020	7/22/2020
57120157	870108	8/7/2020	7/23/2020
57120157	870108	8/7/2020	7/24/2020
57120157	870108	8/7/2020	8/3/2020
57120157	870108	8/7/2020	8/4/2020

CleanedDate+2BD = 8/9/2020 and PlanDate Started From 7/22/2020 to 8/4/2020, In this case CleanedDate not present in PlanDate, hence this data should be blank.

2. Need to check PlanDate to CleanedDate + 2 Business days (function - Networkdays (Monday to Friday)) ,

In below case

CleanedDate+2BD = 8/6/2020 and PlanDate Started from 8/5/2020. In this case the PlanDate should be equal to 8/6/2020 or whatever greater is present in PlanDate.

OrderLine	Parts	CleanedDate	PlanDate	OutputDate
CWET157	YTR789	8/4/2020	8/5/2020
CWET157	YTR789	8/4/2020	8/9/2020	8/9/2020
CWET157	YTR789	8/4/2020	8/12/2020	8/12/2020

Thanks in advance for your input.

Re: Date according Business Days

Taoufiq_Zarra — Wed, 14 Oct 2020 13:03:32 GMT

@rakeshkumar1890 May be like this

load *,if(LastWorkDate(CleanedDate+1, 2)<PlanDate,PlanDate) as OutputDate; LOAD OrderLine, Parts, Date#(CleanedDate,'M/D/YYYY') as CleanedDate, Date#(PlanDate,'M/D/YYYY') as PlanDate INLINE [ OrderLine, Parts, CleanedDate, PlanDate 57120157, 870108, 8/7/2020, 7/22/2020 57120157, 870108, 8/7/2020, 7/23/2020 57120157, 870108, 8/7/2020, 7/24/2020 57120157, 870108, 8/7/2020, 8/3/2020 57120157, 870108, 8/7/2020, 8/4/2020 57120157, P11888, 8/7/2020, 7/29/2020 57120157, P11888, 8/7/2020, 7/30/2020 57120157, P11888, 8/7/2020, 7/31/2020 CWET157, 979797, 8/3/2020, 8/5/2020 CWET157, 979797, 8/3/2020, 8/9/2020 CWET157, 979797, 8/3/2020, 8/10/2020 CWET157, YTR789, 8/4/2020, 8/5/2020 CWET157, YTR789, 8/4/2020, 8/9/2020 CWET157, YTR789, 8/4/2020, 8/12/2020 ];

output:

8/7/2020+2BD is 11 not 9 ?

Re: Date according Business Days

rakeshkumar1890 — Thu, 15 Oct 2020 17:44:26 GMT

Big thanks Taoufiq