How could I generate an end date for each row of data based on the start date of another row?

wilbaile

I have a dataset that gives all records for all evaluations submitted. I need to generate an additional column in the dataset that records the summary_end_date. The start date of an evaluation is the value in the row for the summary_date column. The summary_end_date needs to be the summary_date of the next iterative evaluation submitted.

An evaluation period is defined as the start date of any given evaluation up until the next follow up evaluation is completed.

Here is an example of the dataset:

Each client has multiple summaries designated by the summary_id & summary_order columns.

Each summary that is active would set the summary_end_date to NULL

For all summaries that are not active (i.e. "completed"), the summary_end_date must be the summary_date of the evaluation that immediately preceded it as determined by the summary_order column.

Here is an example of the desired output:

This would ultimately become a dataset that could be exploded out by day using the DOMO Date Calendar dataset such that one would be able to derive which summary_id was the active evaluation for each day in a given time period... (This part is not difficult it's getting the above situated that is causing issues...)

RobSomers

@wilbaile Use the Rank & Window tile in Magic ETL with the Lead function with an offset of 1 and ordering by summary_date, and partition by client_id. This will get the summary_date from the next row, and will look at it by client_id so the last row of each client_id should still be null.

RobSomers

@wilbaile You can do the ordering and everything within the Rank & Window tile. It has options for ordering the data to take care of the dates being in the correct order as well as partitioning to take care of the client_id

https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window

RobSomers

@wilbaile Use the Rank & Window tile in Magic ETL with the Lead function with an offset of 1 and ordering by summary_date, and partition by client_id. This will get the summary_date from the next row, and will look at it by client_id so the last row of each client_id should still be null.

wilbaile

Hi RobSommers!

I ordered the dataset in the question as an example so I just want to clarify the following;

This would require the dataset to be ordered properly as the lead by 1 offset can basically be thought of as a literal "move down 1 row" for each record correct. Therefor in order for the lead function to do it's job the data must first be ordered to accommodate that logic?

wilbaile

Amazing Thank You!

RobSomers

@wilbaile You can do the ordering and everything within the Rank & Window tile. It has options for ordering the data to take care of the dates being in the correct order as well as partitioning to take care of the client_id

https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window