According Plan date and Max or Min Orderdate, Required value in new column

[rakeshkumar1890]

Hi Everyone,

Please help me out with this query.

I need output column with the help of Max PlanOrderDate and AvailableOrderDate (If Equal with previous date : output - "Not changed", If Max with previous date : output - "A", if Min with previous date : output - "B"

KeyOrder	Seq	AvailableOrderDate	PlanOrderDate	Output
A001	1	2020-05-25	2020-05-18	Not Changed
A001	2	2020-05-23	2020-05-19	Not Changed
A001	3	2020-05-23	2020-05-20	Not Changed
A002	1	2020-05-01	2020-04-25	A
A002	2	2020-05-05	2020-04-26	A
A003	1	2020-05-10	2020-04-25	B
A003	2	2020-05-05	2020-04-26	B

Kind Regards

[Taoufiq_Zarra]

@rakeshkumar1890 

I don't get it. Can you give an example? for example this line ?

A001

1

2020-05-25

2020-05-18

Not Changed

[rakeshkumar1890]

Hi Taoufiq,

Thanks for approaching to this query.

1. In keyorder A001, You can see that maximum PlanOrderDate is "2020-05-20" and AvailableOrderDate on same row is 2020-05-23 and on Previous PlanOrderDate (2020-05-19), AvailableOrderDate is 2020-05-23

Hence here on both cases AvailableOrderDate are similar - In this case Output should be "Not Changed"

Also you can use Seq for max(3) and Previous(2) in place of PlanOrderDate

2. In keyorder A002, You can see that maximum PlanOrderDate is "2020-04-26" and AvailableOrderDate on same row is 2020-05-05 and on Previous PlanOrderDate (2020-04-25), AvailableOrderDate is 2020-05-01

Hence AvailableOrderDate(2020-05-05 )is greater than Previous AvailableOrderDate (2020-05-01) - In this case output should be "A"

3. In keyorder A003, You can see that maximum PlanOrderDate is "2020-04-26" and AvailableOrderDate on same row is 2020-05-05 and on Previous PlanOrderDate (2020-04-25), AvailableOrderDate is 2020-05-10

Hence AvailableOrderDate(2020-05-05 )is less than Previous AvailableOrderDate (2020-05-10) - In this case output should be "B"

[Taoufiq_Zarra · Accepted solution]

Hi  @rakeshkumar1890 ,

One solution :

Data:

LOAD * INLINE [
    KeyOrder, Seq, AvailableOrderDate, PlanOrderDate
    A001, 1, 2020-05-25, 2020-05-18
    A001, 2, 2020-05-23, 2020-05-19
    A001, 3, 2020-05-23, 2020-05-20
    A002, 1, 2020-05-01, 2020-04-25
    A002, 2, 2020-05-05, 2020-04-26
    A003, 1, 2020-05-10, 2020-04-25
    A003, 2, 2020-05-05, 2020-04-26
];

left join load KeyOrder,
FirstSortedValue(AvailableOrderDate,-Date#(PlanOrderDate,'YYYY-MM-DD')) as Tmp1,
FirstSortedValue(AvailableOrderDate,-Date#(PlanOrderDate,'YYYY-MM-DD'),2) as Tmp2

resident Data group by KeyOrder;

output:
noconcatenate

load 
 KeyOrder, Seq, AvailableOrderDate, PlanOrderDate,
 if(Tmp1=Tmp2,'Not Changed',if(Tmp1>Tmp2,'A','B')) as output
 
 resident Data;
drop table Data;

output :

[rakeshkumar1890]

Thanks Taoufiq