Rank Question

helpme12345
helpme12345 Member
in Magic ETL

I have a list of companys that have different subscription intervals (monthly, annual) and I'm seeing duplicate rows. To mitigate this I want to have one line item for each company, I have tried using Rank and Window and it removes all rows of data. How do I properly get one row for each company using most recent create date?

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited November 7 Answer ✓

    Is this how your rank is configured? It should definitely not be removing all rows of data.

    Screenshot 2024-11-07 at 11.30.00 AM.png

    You could instead use the Group By tile and select MAX as the aggregation on Create Date.

    Screenshot 2024-11-07 at 11.28.58 AM.png

    Keep in mind that you'll need to include all columns in step 1 that you want to remain in the data.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 7 Answer ✓

    Is this how your rank is configured? It should definitely not be removing all rows of data.

    Screenshot 2024-11-07 at 11.30.00 AM.png

    You could instead use the Group By tile and select MAX as the aggregation on Create Date.

    Screenshot 2024-11-07 at 11.28.58 AM.png

    Keep in mind that you'll need to include all columns in step 1 that you want to remain in the data.

    If I solved your problem, please select "yes" above

Categories