topic Re: According Plan date and Max or Min Orderdate, Required value in new column in QlikView

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

rakeshkumar1890 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Taoufiq_Zarra — Thu, 02 Jul 2020 12:45:47 GMT

@rakeshkumar1890

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

A001

2020-05-25

2020-05-18

Not Changed

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

rakeshkumar1890 — Thu, 02 Jul 2020 13:29:27 GMT

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"

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

Taoufiq_Zarra — Thu, 02 Jul 2020 13:59:55 GMT

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 :

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

rakeshkumar1890 — Fri, 03 Jul 2020 05:19:59 GMT

Thanks Taoufiq