Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Transaction log to state change transformation

Waynemk
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


 
1 REPLY 1
jarrod
ACE Emeritus
ACE Emeritus

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