topic Re: Calculation and Output only with last two rows in QlikView

Calculation and Output only with last two rows

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

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

Re: Calculation and Output only with last two rows

lironbaram — Wed, 22 Jul 2020 06:21:03 GMT

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;

Re: Calculation and Output only with last two rows

rakeshkumar1890 — Wed, 22 Jul 2020 13:34:15 GMT

Exactly the same what I want.

Thanks Lironbaram