Magic ETL Question: Rank and Window and Filter

giannzi

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!

DashboardDude

@giannzi why don't you just filter for majors ending with BS before the rank?

MarkSnodgrass

Agree with John. Before your rank, add a filter tile and choose formula and use this formula:
RIGHT(`ACAD_PLAN`,2) = 'BS'