topic Re: Script help??? in QlikView

Script help???

amit_saini — Tue, 12 Jan 2016 04:15:18 GMT

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,

Re: Script help???

tresB — Tue, 12 Jan 2016 04:18:36 GMT

Any sample data set?

Re: Script help???

amit_saini — Tue, 12 Jan 2016 04:22:01 GMT

PFA

Thanks,

Re: Script help???

sunny_talwar — Tue, 12 Jan 2016 04:31:17 GMT

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???

tresB — Tue, 12 Jan 2016 04:32:14 GMT

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

Re: Script help???

amit_saini — Tue, 12 Jan 2016 04:32:57 GMT

Sorry PFA,

Thanks,
AS

Re: Script help???

amit_saini — Tue, 12 Jan 2016 04:34:07 GMT

PFA

Re: Script help???

amit_saini — Tue, 12 Jan 2016 04:39:00 GMT

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???

amit_saini — Tue, 12 Jan 2016 04:43:06 GMT

Hi Sunny,

Result should be like below:

Because the Blue numbers are present for 3 continuous years, see below:

Thanks,
AS

Re: Script help???

sunny_talwar — Tue, 12 Jan 2016 04:53:03 GMT

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???

tresB — Tue, 12 Jan 2016 05:34:45 GMT

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.