Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

I wanna cross information from 2 columns and find the last OK Status

wcamarg3
8 - Asteroid

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 numberStatusOperation
10001OK17.A
10002OK10.B
10003OK10.C
10004-17.A
10005-10.B
10006-10.C
10007-17.A
10008-10.B
10009OK10.C
10010NOK17.A
10011OK10.B

 

 

Output - 2nd table (Desirable)

 

Sequence numberStatusOperation
10004-17.A
10007-17.A
10010NOK17.A

 

 

Does anyone know how to do it?

 

 

Many thanks.

5 REPLIES 5
AngelosPachis
16 - Nebula

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

 

AngelosPachis_0-1623865235403.png

 

Hope that works, let me know if that worked for you.

 

Cheers,

 

Angelos

wcamarg3
8 - Asteroid

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!

AngelosPachis
16 - Nebula

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

 

 

apathetichell
18 - Pollux

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.

wcamarg3
8 - Asteroid

Thank you very much for sharing your expertise @AngelosPachis !

Labels