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 IDEdit DatePrevious ValueNew ValueCurrent ValueAA1236/22/20 12:00 AMRyan FitzpatrickSam DarnoldSam DarnoldAA1236/21/20 12:00 AMMark SanchezRyan FitzpatrickSam DarnoldAB1236/21/20 9:00 PMEli ManningDaniel JonesDaniel JonesAB1236/21/20 2:00 AMKerry CollinsEli ManningDaniel JonesAA1236/18/20 12:00 AMChad PenningtonMark SanchezSam DarnoldAA1236/10/20 12:00 AMJoe NamathChad PenningtonSam DarnoldAB1236/20/20 12:00 AMPhil SimmsKerry CollinsDaniel JonesAB1236/1/20 12:00 AMFran TarkentonPhil SimmsDaniel Jones Desired Output: Account6/1/20206/2/20206/3/20206/4/20206/5/20206/6/20206/7/20206/8/20206/9/20206/10/20206/11/20206/12/20206/13/20206/14/20206/15/20206/16/20206/17/20206/18/20206/19/20206/20/20206/21/20206/22/20206/23/2020AA123Joe NamathJoe NamathJoe NamathJoe NamathJoe NamathJoe NamathJoe NamathJoe NamathJoe NamathJoe NamathChad PenningtonChad PenningtonChad PenningtonChad PenningtonChad PenningtonChad PenningtonChad PenningtonMark SanchezMark SanchezMark SanchezMark SanchezRyan FitzpatrickSam DarnoldAB123Fran TarkentonPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsPhil SimmsKerry CollinsDaniel JonesDaniel Jones Any thoughts?

Historical TIme Series

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

Chad Pennington

Mark Sanchez

Ryan Fitzpatrick

Sam Darnold

AB123

Fran Tarkenton

Phil Simms

Kerry Collins

Daniel Jones

Any thoughts?

Output

Parse

Date Time

Preparation

Time Series

Accepted answers

RolandSchubert

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

HistoricTimeSeries.yxmd

All comments

RolandSchubert

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

HistoricTimeSeries.yxmd

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

SnapshotTable.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703