Hi all,
I hope anyone can help me.
I have a table with 3 columns (Sequence number, Status and Operation). The column status is related to a periodic check.
I would like to generate a table which verify the last "Status" per "Operation" and if there is a "NOK" it will generate a new table list considering only the related Operation with all lines among the NOK to the one first data after the last OK for that "Operation".
Below find an example to illustrate what I want. The "Sequence Number" "10007" has "NOK" "Status" and is related to operation "17.A" then it should return the lines from the sequences 10004, 10007 and 10010.
Original table.
Sequence number | Status | Operation |
10001 | OK | 17.A |
10002 | OK | 10.B |
10003 | OK | 10.C |
10004 | - | 17.A |
10005 | - | 10.B |
10006 | - | 10.C |
10007 | - | 17.A |
10008 | - | 10.B |
10009 | OK | 10.C |
10010 | NOK | 17.A |
10011 | OK | 10.B |
Output - 2nd table (Desirable)
Sequence number | Status | Operation |
10004 | - | 17.A |
10007 | - | 17.A |
10010 | NOK | 17.A |
Does anyone know how to do it?
Many thanks.
Solved! Go to Solution.
Hey @wcamarg3 ,
Here's a way that you can do it. You can make use of a summarize tool to find the last status per operation. Then use a filter tool to separate those operations that have an NOK status and join that to the main table. Use a sort tool to make the most recent status per operation appear first and the flag all records that appear after the first OK (including the first OK). Finally, you need to sort back to the appropriate order
Hope that works, let me know if that worked for you.
Cheers,
Angelos
Hi @AngelosPachis ! Thank you so much. It works with this range of data, but if the last status from an operation is "OK" and I have any "NOK" among the sequence it will be missed.
I would like to check the whole lines from column status and if there is any NOK it correlates the operation and return me a list with all lines between the NOK and the last OK from that specific operation.
If you or anyone else have any other idea please let me know.
I'll keep waiting an alternative solution.
Many thanks!
I see @wcamarg3 , here's another way that might work, similar logic but you don't use a summarize tool to keep the last, rather you filter out the NOKs and then join to find those operations.
Hope that helps, please let me know if that works better.
Cheers,
Angelos
Can you post some better data? Trying to get a workflow to fit that scenario with the limited data you posted isn't working - at least for me. There's going to be a multi-row formula and an idenitfier for Noks and a summarize tool - but it's not coming together with what you posted.
Thank you very much for sharing your expertise @AngelosPachis !