10 Replies Latest reply: Jan 12, 2016 12:40 AM by Tresesco B RSS

    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

        • Re: Script help???
          Tresesco B

          Any sample data set?

            • Re: Script help???
              Amit Saini

              PFA

               

              Thanks,

              AS

                • Re: Script help???
                  Sunny Talwar

                  Hey Amit

                   

                  Your sample attached has different field names compared to the image you posted in your initial post. Would you be able to elaborate on how the two field names map to each other. And also what is your desired result from the attached sample?

                  • Re: Script help???
                    Tresesco B

                    Create a sample qvw with inline data so that some scripting can be modified.

                      • Re: Script help???
                        Amit Saini

                        PFA

                          • Re: Script help???
                            Sunny Talwar

                            May be this:

                             

                            Table:

                            LOAD [AC Serial],

                                [Task Numbers],

                                [Task Description],

                                [Task Findings],

                                [Modified Date],

                                Year

                            FROM

                            [Qlik.xls]

                            (biff, embedded labels, table is Sheet1$);

                             

                            FinalTable:

                            LOAD *,

                              If([AC Serial] = Peek('AC Serial'),

                              If(Year = Peek('Year') + 1, RangeSum(1, Peek('Flag')), 0), 1) as Flag

                            Resident Table

                            Order By [AC Serial], Year;

                             

                            Join (FinalTable)

                            LOAD [AC Serial],

                              'Yes' as Output

                            Resident FinalTable

                            Where Flag = 3;

                             

                            FinalFinalTable:

                            LOAD [AC Serial],

                                [Task Numbers],

                                [Task Description],

                                [Task Findings],

                                [Modified Date],

                                Year,

                                If(Len(Trim(Output)) = 0, 'No', Output) as Output

                            Resident FinalTable;

                             

                            DROP Tables Table, FinalTable;

                          • Re: Script help???
                            Amit Saini

                            Hi ,

                             

                            Sample data:

                             

                            load * Inline [

                             

                            AC Serial,    Task Numbers    ,Task Description    ,Task Findings ,    Modified ,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

                            ];

                             

                             

                            Thanks,
                            AS

                              • Re: Script help???
                                Tresesco B

                                 

                                 

                                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.