Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

amit_saini
Honored Contributor 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

Re: Script help???

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;

10 Replies
MVP
MVP

Re: Script help???

Any sample data set?

amit_saini
Honored Contributor III

Re: Script help???

PFA

Thanks,

AS

Re: Script help???

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?

MVP
MVP

Re: Script help???

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

amit_saini
Honored Contributor III

Re: Script help???

Sorry PFA,

Thanks,
AS

amit_saini
Honored Contributor III

Re: Script help???

PFA

amit_saini
Honored Contributor III

Re: Script help???

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
Honored Contributor III

Re: Script help???

Hi Sunny,

Result should be like below:

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

Thanks,
AS

Re: Script help???

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;