Alteryx Designer Discussions

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

How to flag duplicates and calculate time between duplicates records?

7 - Meteor

I have COVID vaccination data:

 

MemberIDVaccinationDateFullyVaccinatedDays Between Vaccinations
JohnDoe1/6/2021Y28
JohnDoe2/3/2021Y28
JaneSmith1/20/2021NTBD

 

I'm provided with columns 1 and 2 and I'm trying to figure out how to create column 3 and 4 across a large dataset. I've found some articles on dealing with duplicates that say to use the summarize tool, but I can't seem to get that to work.

7 - Meteor

I might prefer to see it as a single row per memberid, so final output might look like

 

MemberIDVaccination1Vaccination2Vaccination3FullyVaccinatedDays Between Vaccinations
JohnDoe1/6/20212/3/2021 Y28
      

 

I put three because I've already seen a record that has 2 vaccines on the same day.

15 - Aurora

@DataMafia 
Hope I understand your logic correctly.

0114-DataMafia.PNG

7 - Meteor

@Qiu since you seem to have a firm grasp of how to do this, may I ask you one more question? You've answered my main question, but I'm trying to modify your method to include more elements. 

 

 

Date Added to FileNameAdmin. DateProduct HCPCS/CPT CodeProduct NDCVaccine Admin. CodeAdmin. BrandSubmission Clarification Code
1/12/2021JoeSchmoe12/29/202091300 0001APfizer 
1/25/2021JoeSchmoe1/29/202191300 0002APfizer 
1/12/2021JohnDoe1/2/202191301 0011AModerna 
1/25/2021JohnDoe1/2/202191300 0002APfizer 
1/12/2021JohnDoe2/3/202191301 0012AModerna 
1/25/2021JaneSmith1/29/202191300 0002APfizer 

 

Lets say I want to roll this up and remove duplicates, which we didn't do in the last version. 

 

So final output for Joe would be something like

 

 Vaccine Admin 1Vaccine Admin Code 1Vaccine Brand 1Vaccine Admin 2Vaccine Admin Code 2Vaccine Brand 2     
JoeSchmo12/29/20200001APfizer1/29/20210002APfizer     

 

I think it's more of the same. I also didn't give an example output for the "error" of two vaccines on the same day, but I have to run. I really appreciate the help, learn something new each time I post.

 

15 - Aurora

@DataMafia 
I hope I also had  a firm grasp of your intention. 😁0114-DataMafia-R1.PNG

Labels