Case Statement help

user028582
user028582 Member
in Magic ETL

I have an ETL with several joins and calculations. My final output is good except for this last piece: I would like to identify in a formula that when the "ID" column has the same # but the "Status" column is different, then I want to keep the status result from the "last update date" column. Is there a formula I can use for this?

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @user028582 Can you please share an example of what your data looks like and the desired result, removing any sensitive information? That will help us answer your question more effectively.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Take a Group By tile to group by the ID and Status Fields, take the MAX of your UPDATED field then inner join your data back into your original dataset based on the ID, Status Fields, and where the UPDATE fields match. This will remove any records where it's not the last updated value.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Take the output from the last formula tile and feed that into a group by tile. Then feed the output of that and the same formula tile to a Join tile and do the inner join. Then take the output of the join and feed it to your output dataset.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @user028582 Can you please share an example of what your data looks like and the desired result, removing any sensitive information? That will help us answer your question more effectively.

  • user028582
    user028582 Member

    I yes, from the highlighted rows I want to keep the second row. I highliighted the column headings I would use to make this determination


    image.png

    Note, there is not always a "closed" option, sometimes the two rows will both say "open" but then I would need the latest updated row.


    This is the ETL (don't judge I'm brand new at this 😂) I'm sure there is an easier way to do this!


    image.png


  • user028582
    user028582 Member


  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Take a Group By tile to group by the ID and Status Fields, take the MAX of your UPDATED field then inner join your data back into your original dataset based on the ID, Status Fields, and where the UPDATE fields match. This will remove any records where it's not the last updated value.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user028582
    user028582 Member

    Grant thank you! I have never done a "group by" before. On my ETL where would I put the group by tile (connected to what?) and would I connect it back to "All State Resolutions" using an inner join? Thank you so much for your help

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Take the output from the last formula tile and feed that into a group by tile. Then feed the output of that and the same formula tile to a Join tile and do the inner join. Then take the output of the join and feed it to your output dataset.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user028582
    user028582 Member

    It worked THANK YOU SO MUCH!!

Categories