Historical TIme Series

[adnuta1]

Hi All - I have a table from SF which tracks changes to certain fields; the table includes an account ID, an edit date, the old value, the new value, and the current value.

I want to create a snapshot table (i.e. the value as of a certain date) using this data. My thought was to work backwards from the current values to display the old values for dates equal to or before the edit date.

Data:

Account ID	Edit Date	Previous Value	New Value	Current Value
AA123	6/22/20 12:00 AM	Ryan Fitzpatrick	Sam Darnold	Sam Darnold
AA123	6/21/20 12:00 AM	Mark Sanchez	Ryan Fitzpatrick	Sam Darnold
AB123	6/21/20 9:00 PM	Eli Manning	Daniel Jones	Daniel Jones
AB123	6/21/20 2:00 AM	Kerry Collins	Eli Manning	Daniel Jones
AA123	6/18/20 12:00 AM	Chad Pennington	Mark Sanchez	Sam Darnold
AA123	6/10/20 12:00 AM	Joe Namath	Chad Pennington	Sam Darnold
AB123	6/20/20 12:00 AM	Phil Simms	Kerry Collins	Daniel Jones
AB123	6/1/20 12:00 AM	Fran Tarkenton	Phil Simms	Daniel Jones

Desired Output:

Account

6/1/2020

6/2/2020

6/3/2020

6/4/2020

6/5/2020

6/6/2020

6/7/2020

6/8/2020

6/9/2020

6/10/2020

6/11/2020

6/12/2020

6/13/2020

6/14/2020

6/15/2020

6/16/2020

6/17/2020

6/18/2020

6/19/2020

6/20/2020

6/21/2020

6/22/2020

6/23/2020

AA123

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Joe Namath

Chad Pennington

Chad Pennington

Chad Pennington

Chad Pennington

Chad Pennington

Chad Pennington

Chad Pennington

Mark Sanchez

Mark Sanchez

Mark Sanchez

Mark Sanchez

Ryan Fitzpatrick

Sam Darnold

AB123

Fran Tarkenton

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Phil Simms

Kerry Collins

Daniel Jones

Daniel Jones

Any thoughts? 

[RolandSchubert · Accepted solution]

Hi @adnuta1 ,

it looked easy at first, but had some special challenges,  I've attached a sample workflow.

.

Let me know, if it works for you.

Best,

Roland

[JonMcMillin]

Hi @adnuta1,

In general I would suggest learning to read the structure of the data you are receiving, however if you are giving the snapshot table to a less technical user you can try the attached workflow.

Note there are 2 tools with hard coded dates, that you may need to make dynamic if you are looking to run regularly based on your business requirements.

In high level logic the workflow generate rows between a start and end date for each account, and then joins this onto the data stream and then leverages a cross tab tool to get the dates as columns.

EDIT: @RolandSchubert  beat me too it 🙂 glad we have similar approaches

 

[adnuta1]

Thanks Roland; quick follow up (hopefully). I have another table showing all the quarterbacks for each team at the moment and I'd like to append that table to the workflow which you shared if the team isnt in the original table with the assumption being that if it isnt in the first table it hasnt changed.