Grouping + Condition Check + Output

Question

I have a data set as below. I would basically need to check the first row of every unique LEID and see if the Exit column has a value "y". I would need to filter out such rows first.

1) I have used the Tile Tool to group by LEID and then used the Filter Tool to get only the records that match the criteria TileSequenceNum = 1 AND Exit = 'y'

2) I have used the Join Tool to join the output of Step 1 with the original input data set to get back the other rows of the above matched criteria.

3) From Step 2 I would need to arrive at an output where I would need to return only if the Exit value for a LEID is 'y' and the other Exit values of the same LEID is either "Null" or "n"

Desired Output is

Grouping_Condition_Output.yxmd

Ladarthure · Accepted Answer

Hi @surajmthomas,

I draw a solution for your problem, one way to do it would be to do "tops", like if there is y it's ok for each row, then getting the max for each LEID, and finaly filtering the LEID which answers the differents conditions. Then you can join with original data to keep only what you need. Find the workflow attached!

Jean-Balteryx · Accepted Answer

Hi @surajmthomas ,

Here is a workflow that produce your desired output !

atcodedog05 · Accepted Answer

Hi @surajmthomas

Here is my take on the usecase

Workflow:

Above branch is checking is seq=1 and exit='y'. Lower branch is get getting count of 'y' for LEID and checking is count=1. If seq=1 and exit='y' and count of 'y'=1 then first row is 'y' and only one 'y' exits which is in first row. Taking common LEID amongst both using join tool.

Hope this helps : )