Magic ETL Filter Question

giannzi

Hi,

I have two columns: Academic Plan (ACAD_PLAN) and Effective Date (EFFDT). Currently, I am using the Rank & Window and Filter tiles to retrieve the most recent EFFDT and, consequently, the latest ACAD_PLAN.

However, when I apply the Rank & Window tile, sort EFFDT in descending order, and filter for Row Number = 1, I only get MILANG. Instead, I need to retrieve all ACAD_PLAN values (CSBS, MILDRSHP, and MILANG) associated with the latest EFFDT (May 28, 2024).

Is there a way to achieve this while ensuring I capture all academic plans tied to the most recent effective date?

Thanks!

MarkSnodgrass

You likely need to add Academic Plan to your partition in the rank & window tile. This will give you a row number of 1 for each academic plan sorting by the effective date descending.