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

among individuals counting activities between events

jbuszin
8 - Asteroid

Could use some advice on how you would do this in alteryx. Below is data from a single person. The first column is a marker between events (for example, has a baby, and this person had 3 babies). The second column is the number of activities that happened between events. Sticking to the example, that lets call them doctor appointments. So, this person had 14 doctor appointments before baby 1, 26 after baby 1 and before baby 2, and 14 after baby 2 and before baby 3. Note the dataset actually has many individuals (with a unique ID) in there with different numbers of babies.

 

I need a third column that can tell me the total number of appointments between babies and I'm totally blanking on this.

 

Had Baby# appointments
01
00
01
01
01
01
01
01
01
01
01
01
01
01
01
10
01
00
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
01
10
01
01
01
01
01
01
01
01
01
01
01
01
01
01
10

 

 

6 REPLIES 6
grossal
15 - Aurora
15 - Aurora

Hi @jbuszin,

 

I am not sure what the format should actually look like in the new column, but I managed to sum it up. 

 

grossal_0-1585340902852.png

 

Output:

Had Baby#Sum_# appointments
014
126
214
30

 

What's happening:

1) I make transform the baby column to an increasing counter and let it go up everytime we see a 1.

2) Summarize it. Grouped by 'Had Baby' and Sum of appointments.

 

 

Can you show me an example of the expected output to further help you?

 

Workflow attached.

 

Best 

Alex

 

AbhilashR
15 - Aurora
15 - Aurora

@jbuszin - attached is my attempt to compute the sum of appointment before each baby

 

AbhilashR_0-1585341868781.png

 

EDIT - I have reattached a modified version of my solution to accommodate for multiple ID's in your data.

jbuszin
8 - Asteroid

Sorry I'm not being clear. I'm trying to create "total appointments between babies". This would be by ID.

 

IDHad Baby# appointmentsAppointments between babies
10114
10014
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
1100
10126
10026
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
10126
1100
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
10114
1100
jbuszin
8 - Asteroid

I have another scenario and was hoping to take your logic to figure out the new scenario but I'm not getting it. Instead of the variable being 0/1 it's now got three options (0, 1, null). Trying to stick to my example, we have doctor appointments and then no births (null) and birth of boy (0) and birth of girl (1). 

 

The data below shows 14 doctor appointments before the birth of a girl. After that birth, there was 1 doctor appointment before the birth of a boy. After that there were 25 appointments and then the birth of a girl. Then 14 appointments before the birth of another girl. Finally, 0 appointments and the birth of a boy.

 

In your very helpful solution you provided the number of appointments where there was a birth. I'm trying to capture the number of appointments before a boy being born and the number of appointments before a girl being born. The difference between this example and what I presented before that you did a solution for is before "birth" was all filled out, 0 or 1, depending on a birth. Now 0 means something and 1 means something and null exists, and it means something (no birth). Can you please provide guidance?

 

appointmentsbirth
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
01
1 
00
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
01
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
1 
01
00
AbhilashR
15 - Aurora
15 - Aurora

@jbuszin - does the attached solution work? look for the section named Latest Solution.

AbhilashR_0-1585514672820.png

 

jbuszin
8 - Asteroid

Thank you so much for your guidance. I'm not laying this out to you all correctly because I'm trying to use an analogy rather than what is in my actual data. Here it is:

 

I have this data where a row represents an activity. There are multiple people in the data and each person can have many activities. The data below is just for one person. I am trying to capture how much marketing activity happens between days between appointments, and I care whether the days between appointments are greater than 30 days or 30 days or less. 

 

The first appointment below happens 128 days after the appointment before (which is the second row, as you see "marketing activity" is a 0). I can figure out how to sum up the marketing activity where the days between appointments is more than 30 days, thanks to a lot of help from you already. That is why from what you provided (14, 26, 14 were all right). I need to sum up the marketing activity where the days between appointments is 30 days or less too.  I thought I could take your logic for the greater than 30 days and apply it to 30 or fewer days but I can't. 

 

The answer should be that, by person, marketing activity should sum to 1 in the first instance where days between appointments is 30 or fewer days (because if you look there's a single row of marketing activity between 128 and 12 in the days between appointments. Then, marketing activity should sum to 0 because in the next instance of 30 or fewer days between appointments it's been just 1 day (prior to that it had been 69 days). Then there is an appointment 7 days later, and before that 7 days later, there are 4 marketing activities and so the sum should be 4. Finally, there's an appointment a day later but there was also a marketing activity before that and so it should be 1. 

 

Complicated but not too complicated I hope! I feel like the solution is on the tip of my tongue but not quite there. I hope I have better explained this now.

 

Days_between_appointments30+daysmarketing activity30+days_new
 01 
 00 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
128101
 01 
12000
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
85101
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
 01 
69101
1000
 01 
 01 
 01 
 01 
7000
 01 
1000
Labels