Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Any sample data set?
PFA
Thanks,
AS
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?
Create a sample qvw with inline data so that some scripting can be modified.
Sorry PFA,
Thanks,
AS
PFA
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
Hi Sunny,
Result should be like below:
Because the Blue numbers are present for 3 continuous years, see below:
Thanks,
AS
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;