Calculation and Output only with last two rows

[rakeshkumar1890]

Hi All,

Need your help

I want to result based on OrderNo, AvailableDate and Max(Plandate) on last rows and else null.

 Same : - If  max(PlanDate) and current Availabledate = Previous Available Date, Group by OrderNo Then Output "Same"

A :  If  max(PlanDate) and current Availabledate > Previous Available Date, Group by OrderNo Then Output "A"

B :  If  max(PlanDate) and current Availabledate < Previous Available Date, Group by OrderNo Then Output "B" 

OrderNo	PlanDate	AvailableDate	OutPut
100	2019-12-17	2019-12-18
100	2019-12-18	2019-12-20
100	2019-12-19	2019-12-31
100	2019-12-20	2019-12-31	Same
200	2019-12-17	2020-01-16
200	2019-12-27	2019-12-31
200	2019-12-30	2019-12-31
200	2019-12-31	2020-01-02	A
300	2020-01-07	2020-01-30
300	2020-01-07	2020-01-30
300	2020-01-07	2020-01-10	B

[lironbaram · Accepted solution]

Hi , 

this script should give you the results you look for 

Table1:
load * inline [
OrderNo,PlanDate,AvailableDate
100,2019-12-17,2019-12-18	 
100,2019-12-18,2019-12-20	 
100,2019-12-19,2019-12-31	 
100,2019-12-20,2019-12-31
200,2019-12-17,2020-01-16	 
200,2019-12-27,2019-12-31	 
200,2019-12-30,2019-12-31	 
200,2019-12-31,2020-01-02
300,2020-01-07,2020-01-30	 
300,2020-01-07,2020-01-30	 
300,2020-01-07,2020-01-10
];
left Join
load OrderNo,
     max(PlanDate) as orderMaxPlanDate
Resident Table1
GROUP By OrderNo;


Table2:
load *,
     if(PlanDate=orderMaxPlanDate and OrderNo=Previous(OrderNo),
     	if(AvailableDate=Previous(AvailableDate),'Same' ,
        	if(AvailableDate<Previous(AvailableDate),'B' ,'A')))		AS Output
Resident Table1
Order by OrderNo,PlanDate,AvailableDate;

drop Table Table1;

[rakeshkumar1890]

Exactly the same what I want.

Thanks  Lironbaram