Transaction log to state change transformation

[Waynemk]

 

I have a transaction log that consists of a customer ID, an activity date and a state entered. I am interested in reshaping to reflect the time spent in a given state. The challenge is that I can’t figure out how to access two rows at a time. I am also not 100% sure thats the best way to go.

Here is a simplified example of what the source data looks like

custID1, date1, state entered A, open

custID1, date2, state entered B, action

custID1, date3, state entered C, action

custID1, date4, state entered C, update (updated without state change)

custID1, date5, state entered D, close

here is what I am looking to build

custID1, date1, state entered A, date2, state entered B

custID1, date2, state entered B, date3, state entered C

custID1, date3, state entered C, date4, state entered C

custID1, date4, state entered C, date5, state entered D

[jarrod · Accepted solution]

Hi Wayne,

If i understand correctly, you want to find the time spend in any state, even if it is an updated version of the state?

in that case, i would just leave them in the format you have in the first example and use the Multi-row tool and group by CustID so you aren't comparing dates across customers.

I made the following module as an example:
 

Here is the text input:



and here is the formula I used to calculate the time spent in each state in days:



Let me know if you want the module as well.

- Jarrod