Filtering Multiple Columns

[JarretS]

I am working on a data set for multiple securities.  I am looking to filter for securities that have a realized loss and an unrealized gain.  I have over 20k of rows so when I filter I want to be left with only securities that have both Realized Loss and Unrealized Gain, but those two columns will never be populated together. So when I use the following, it filters our everything since no security has both of those columns populated.

[Local URL Gain/(Loss)] > 0
AND
[Total  Realized P/L ] < 0

What is the proper filter to use to get both of those criteria to show up?

[Joe_Mako]

Can you provide a sample that is representative of your situation, and what you expect the result to be?

My initial thought is you can use the Summary tool to aggregate your data, grouping by security and then filter.

[JarretS]

See attached.  This is a striped down version of what I am trying to do for confidentiality reasons.

I will have hundreds of securities and I just want the instances where the same security has realized losses and unrealized gain so I kind of need to filter two columns at the same time

[Joe_Mako · Accepted solution]

Looks like you just need to change your "AND" to an "OR", like:

[Realized Gain/Loss] < 0
OR
[Unrealized Gain/Loss] > 0

[JarretS]

Actually I accepted this solution too soon.  See attached.  The OR formula populates anything that has either of those criteria met.  In the attached, HP has realized loss but no unrealized gain but would be included because its filtering for OR not both.

I want all securities that fit both criteria but i cant use the AND filter because both columns are not populated on the same row.

[Joe_Mako]

How do you relate the records to eachother?

If I use record order, only the first couple match.

Is there another column like date or some other dimension?

[JarretS]

The relation is the security name.  Since they are listed multiple times, I am looking for instances where the security name has both a realized loss and unrealized gain.  For example, Apple has has both a realized loss and unrealized gain, but on different rows.  HP has realized loss but not unrealized gain so it should be excluded.

For this part of the workflow, the only common dimension is securiy name.

[Joe_Mako]

How about the attached?

The workflow will add a RecordID so we can later return to the original sort order, filter to records that meet either condition, summarize to the security level of detail, filter out any that is missing either value, join that result back to the filtered result and sort.