Date according Business Days - Part2

[rakeshkumar1890]

Hello Everyone,

Need your help to find out Date After comparing only Business Days With + 2 Days

Table:

Order	PlanDate	RecoveryDate	CleanDate	Output
H3CT74161423	8/10/2020	8/10/2020	6/2/2020
H3CT74161423	8/7/2020	8/10/2020	6/2/2020
H3CT74161423	7/30/2020	8/14/2020	6/2/2020
H3CT74161423	7/16/2020	8/17/2020	6/2/2020
H3CT74161423	7/15/2020	8/17/2020	6/2/2020
H3CT74161423	7/7/2020	8/17/2020	6/2/2020
H3CT74161423	7/6/2020	8/17/2020	6/2/2020
H3CT74161423	7/3/2020	8/17/2020	6/2/2020
H3CT74161423	7/2/2020	8/20/2020	6/2/2020	8/20/2020
H3CT75299167	7/16/2020	-	7/2/2020
H3CT75299167	7/15/2020	7/15/2020	7/2/2020
H3CT75299167	7/15/2020	-	7/2/2020
H3CT75299167	7/14/2020	7/15/2020	7/2/2020
H3CT75299167	7/9/2020	-	7/2/2020
H3CT75299167	7/8/2020	-	7/2/2020
H3CT75299167	7/7/2020	-	7/2/2020
H3CT75299167	7/6/2020	-	7/2/2020	3/31/9999
H3CT75299167	7/5/2020	-	7/2/2020
H3CT75299167	7/3/2020	-	7/2/2020
H3CT75299167	7/2/2020	-	7/2/2020
H3CT75299175	9/7/2020	-	2020-09-30	NA
H3CT75299175	8/24/2020	8/28/2020	2020-09-30
H3CT75299175	8/21/2020	8/28/2020	2020-09-30
H3CT75299175	8/20/2020	8/28/2020	2020-09-30
H3CT75299175	8/14/2020	8/17/2020	2020-09-30
H3CT75299175	8/13/2020	8/17/2020	2020-09-30
H3CT75299175	8/12/2020	-	2020-09-30
H3CT75299175	8/11/2020	-	2020-09-30
0057115310	8/3/2020	8/5/2020	2020-07-27
0057115310	7/30/2020	7/30/2020	2020-07-27
0057115310	7/29/2020	7/17/2020	2020-07-27	7/17/2020
0057115310	7/28/2020	7/15/2020	2020-07-27

Here in my raw data - there are three date fields (CleanDate, PlanDate and RecoveryDate). I Need output field with given below scenorios.

1. In first case we have CleanDate(6/2/2020) + 2BD would be 6/4/2020, Then need to check into PlanDate to Corresponding date 6/4/2020. but we have MinDate is 7/2/2020 and Corresponding ReccoveryDate is  8/20/2020, Hence it would be Output result.

2. In Second case we have CleanDate(7/2/2020) + 2BD would be 7/6/2020, Then need to check into PlanDate to Corresponding date 7/6/2020. but we have in Plandate and Corresponding ReccoveryDate is  blank - blank would be 3/31/9999.

3. In Third Case we have CleanDate(9/30/2020) + 2BD would be 10/2/2020, Plandate is less then CleanDate(9/30/2020) + 2BD, Hence Output Result - NA

4. In Fourth Case we have CleanDate(7/27/2020) + 2BD would be 7/29/2020 and Correspoding Plandate, Recoverydate is 7/17/2020.

Thanks in advance for your input.

[rakeshkumar1890]

I worked first and fourth scenario as below but getting double rows.

Table:

LOAD *,
if(LastWorkDate(CleanDate, 3) = PlanDate,RecoveryDate) as OutputDate1
;

LOAD * Inline [
Order, PlanDate, RecoveryDate, CleanDate
H3CT74161423, 8/10/2020, 8/10/2020, 6/2/2020
H3CT74161423, 8/7/2020, 8/10/2020, 6/2/2020
H3CT74161423, 7/30/2020, 8/14/2020, 6/2/2020
H3CT74161423, 7/16/2020, 8/17/2020, 6/2/2020
H3CT74161423, 7/15/2020, 8/17/2020, 6/2/2020
H3CT74161423, 7/7/2020, 8/17/2020, 6/2/2020
H3CT74161423, 7/6/2020, 8/17/2020, 6/2/2020
H3CT74161423, 7/3/2020, 8/17/2020, 6/2/2020
H3CT74161423, 7/2/2020, 8/20/2020, 6/2/2020
H3CT75299167, 7/16/2020, -, 7/2/2020
H3CT75299167, 7/15/2020, 7/15/2020, 7/2/2020
H3CT75299167, 7/15/2020, -, 7/2/2020
H3CT75299167, 7/14/2020, 7/15/2020 7/2/2020
H3CT75299167, 7/9/2020, -, 7/2/2020
H3CT75299167, 7/8/2020, -, 7/2/2020
H3CT75299167, 7/7/2020, -, 7/2/2020
H3CT75299167, 7/6/2020, -, 7/2/2020
H3CT75299167, 7/5/2020, -, 7/2/2020
H3CT75299167, 7/3/2020, -, 7/2/2020
H3CT75299167, 7/2/2020, -, 7/2/2020
H3CT75299175, 9/7/2020, -, 2020-09-30
H3CT75299175, 8/24/2020, 8/28/2020, 2020-09-30
H3CT75299175, 8/21/2020, 8/28/2020, 2020-09-30
H3CT75299175, 8/20/2020, 8/28/2020, 2020-09-30
H3CT75299175, 8/14/2020, 8/17/2020, 2020-09-30
H3CT75299175, 8/13/2020, 8/17/2020, 2020-09-30
H3CT75299175, 8/12/2020, -, 2020-09-30
H3CT75299175, 8/11/2020, -, 2020-09-30
0057115310, 8/3/2020, 8/5/2020, 2020-07-27
0057115310, 7/30/2020, 7/30/2020, 2020-07-27
0057115310, 7/29/2020, 7/17/2020, 2020-07-27
0057115310, 7/28/2020, 7/15/2020, 2020-07-27
]
;

Left Join(Table)
LOAD Order, Date(Min(PlanDate)) as LatestDate
Resident Table
Group By Order;

Left Join(Table)
LOAD Order,
if(LastWorkDate(CleanDate, 3) < LatestDate,RecoveryDate,OutputDate1) as OutputDate_New
Resident Table
Where LatestDate = PlanDate;

Left Join(Table)
LOAD Order,
if(Not IsNull(OutputDate_New),OutputDate_New,OutputDate1) as OutputDate
Resident Table;

Drop Fields OutputDate_New,OutputDate1,LatestDate;

EXIT SCRIPT;