Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Transaction log to state change transformation

Highlighted
8 - Asteroid
 

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


 
Highlighted
12 - Quasar
12 - Quasar

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:
 
rtaImage.png
Here is the text input:

rtaImage (1).png

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

rtaImage (2).png

Let me know if you want the module as well.

- Jarrod

 
Labels