Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

Script help???

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

1 Solution

Accepted Solutions
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;

View solution in original post

10 Replies
tresesco
MVP
MVP

Any sample data set?

amit_saini
Master III
Master III
Author

PFA

Thanks,

AS

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?

tresesco
MVP
MVP

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

amit_saini
Master III
Master III
Author

Sorry PFA,

Thanks,
AS

amit_saini
Master III
Master III
Author

PFA

amit_saini
Master III
Master III
Author

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

amit_saini
Master III
Master III
Author

Hi Sunny,

Result should be like below:

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

Thanks,
AS

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;