FILTER BASED ON A CRITERIA WITH SAME IDENTIFIERS - SOS318

[giulianosua1]

Hi Guys, 

I have a set of data that I want to filter based on the Desk, for example all the Fixed Income ones, but I also want to include the other leg which shares the same identifier but that could have a different Desk. How do I go about it?

NAME	IDENTIFIER	DESK
JAMES	123	FIXED INCOME
ASHLEY	456	FIXED INCOME
ALAN	123	EQUITY
JACK	456	QUANT
CHUCK	987	ALTERNATIVE
WILLIAM	987	ALTERNATIVE

Output based on the above criteria should be:

NAME	IDENTIFIER	DESK
JAMES	123	FIXED INCOME
ASHLEY	456	FIXED INCOME
ALAN	123	EQUITY
JACK	456	QUANT

[ChrisTX]

Use the Summarize tool, group by Identifier and Desk, and for Name try Action = First.

Chris

[zajaccount · Accepted solution]

Hi,

you could do this by setting a filter:

DESK = "FIXED INCOME"

then, select the identifier column and join it with the "False" leg of the filter. 

After this, union the "True" leg of the filter and the joined leg of the Join tool:

Please find the flow attached - let me know if this works.

[giulianosua1]

So I found a solution, not so clean but it works. 

I created a new column that uses an IF statement, If Contains(Desk, "QIS) then [Identifier] else "No" endif

Once this is executed I filter the identifier based on the results of the new column. Any better way to do it?

[ChrisTX]

Your initial question didn't mention the ability to base logic on a "QIS" value in the Desk field.  Can you provide all of your requirements, and update the sample input and expected output?

[giulianosua1]

Thanks Guys - already solved by using the workflow provided above!

[giulianosua1]

Thanks Zaj! this worked!

[zajaccount]

Perfect, glad I could help 😉