Script help???

[amit_saini]

Hi Folks ,

Need help for the below case:

Condition : Any "AC Serial" which is available in 3 continuous year and having "Task Findings" ='NO FAULT FOUND' ,then show output as "YES" against that AC Serial number else "NO" 

Please help me with script!

Thanks,

AS

[tresesco]

Input:
Load * inline [

"AC Serial", "Task Numbers" ,"Task Description" ,"Task Findings" , Date ,Year
560XL-5005 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-04-05 ,2013
560XL-5005 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-05-07 ,2014
560XL-5012 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-06-10 ,2013
560XL-5012 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-06-18 ,2014
560XL-5013 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2012-11-05 ,2012
560XL-5016 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-12-09 ,2013
560XL-5018 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2012-07-09 ,2012
560XL-5018 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-07-03 ,2013
560XL-5018 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-07-09 ,2014
560XL-5100 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-03-15 ,2013
560XL-5100 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-03-04 ,2014
560XL-5102 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2012-06-11 ,2012
560XL-5102 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-07-03 ,2013
560XL-5102 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-07-29 ,2014
560XL-5107 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-07-17 ,2014
560XL-5114 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2013-03-14 ,2013
560XL-5114 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2014-03-10 ,2014
560XL-5117 ,12-90-20-610 ,Shimmy Damper Servicing  ,NO FAULT FOUND ,2015-10-05 ,2015

];

temp:
load
*,
If([Task Findings]='NO FAULT FOUND' and Peek([Task Findings])='NO FAULT FOUND' and Peek([Task Findings],-2)='NO FAULT FOUND' and
  Peek(Year)=Year-1 and Peek(Year,-2)=Year-2 and
    peek([AC Serial])=[AC Serial],
    'YES', 'NO') as Flag
 
Resident Input Order by [AC Serial],Year;

Flag:
Load
      [AC Serial],
      Flag
Resident temp Where Flag='YES';

load
      [AC Serial],
      Flag
Resident temp Where Flag<>'YES';

DROP Table Input;
Final:
Load
      "AC Serial",    "Task Numbers"    ,"Task Description"    ,"Task Findings" ,    Date    ,Year
Resident temp;

DROP Table temp;

This could be another way.