Magic ETL Filter Question

giannzi
giannzi Member
in Magic ETL

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!



image.png
Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories