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

wilbaile
wilbaile Member
in Magic ETL

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.

domo-data-set.png


Here is an example of the desired output:

desired-domo-data-set.png

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...)

Tagged:

Best Answers

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • wilbaile
    wilbaile Member

    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
    wilbaile Member

    Amazing Thank You!

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Categories