Filtering a column that exist in another column

[ringocheng618]

Hello,

I am given four columns: STOCK_ISSUER, YEAR, QUARTER, and VALUE. Is it possible to filter stock that exist in specific year and quarter, but not in other year and quarter?

For instance, I want to test stocks that are invested in 2020 Q1 but not in 2020 Q2 and Q3, how can I do so?

[patrick_digan]

@ringocheng618 Can you provide some sample data or dummy data? What you're asking can certainly be accomplished, but the specific answer would depend on how your data is oriented.

[skeen503979]

@ringocheng618 

You can definitely accomplish that either using multiple filters (per column) or writing a custom filter.  Attached and below are how to configure the filter tool for what you're wanting.

[ringocheng618]

Hello I have created a dummy data.

Obviously data would not be that small. But what I want to know is that, with that many stock issuers, how can I know which stock is not invested between 2020 Q2-Q4, but its invested prior to that.

For instance, Delta Air Lines in this data can be easily seen because we saw it appear prior to 2020 Q2, but just no longer here when it's 2020 Q2.

Thanks in advance!

[danilang]

Hi @ringocheng618 

To get a dynamic solution, you'll need more than just filters.  

The formula tool builds a date for each quarter in the from 2020-01-01.  The top summarize finds the maximum overall date and the bottom one finds the max date for each Issuer.  Join these on Max_Date and look at the R output.   These will be the records that stops investing before the last quarter

Dan