Case Statement help

user028582

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?

MichelleH

@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

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.

GrantSmith

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.

MichelleH

@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

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

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!

user028582

GrantSmith

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.

user028582

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

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.

user028582

It worked THANK YOU SO MUCH!!