Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
I don't get it. Can you give an example? for example this line ?
A001 | 1 | 2020-05-25 | 2020-05-18 | Not Changed |
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"
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 :
Thanks Taoufiq