Magic ETL Question: Rank and Window and Filter

Hi,

I’m trying to retrieve the current major for each student. Specifically, I need to select the ACAD_PLAN that ends with "BS" (for example: "BUSMBS").

Once I have those, I need to find the most recent EFFDT (effective date) that is less than or equal to today’s date, because I want to exclude any future majors.

Currently, I’m using Rank and Window to rank the EFFDT within each ACAD_PLAN, and I filter to keep only the first rank, which gives me the most recent effective date. However, I’m struggling to filter only the majors where the ACAD_PLAN ends with "BS".

Can you guide me on how to apply that condition correctly?

Thank you!