cancel
Showing results for 
Search instead for 
Did you mean: 

Removing duplicates

SOLVED
Highlighted
fangyi189
Asteroid

Hi all,

 

Currently my data is like this :

 

ActivityStart_DateEnd_Date
Kayaking1/09/172/09/17
Kayaking1/09/172/09/17
Running3/09/173/09/17

 

I want to alter it so that if the same activity, in this case "Kayaking" starts on the same start date and end date, it'll only take one row of the data instead of both.

 

So end result will be :

 

ActivityStart_DateEnd_Date
Kayaking1/09/172/09/17
Running3/09/173/09/17

 

Thanks!

Alteryx Certified Partner
Alteryx Certified Partner
Try the UNIQUE tool. Select the check boxes for

Activity Start_Date End_Date

That should do the trick.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

fangyi189
Asteroid

Hi Mark,

 

Great answer! What if my data is like this :

 

ActivityStart_DateEnd_DateTotal days
Kayaking1/09/172/09/172
Kayaking2/09/172/09/171
Running3/09/173/09/171

 ( I changed the start_date for the second row kayaking)

 

And I want to make sure that the same activity, kayaking doesn't collide with each other on any dates. So if first row's kayaking ends on 2/09/17 and second row's kayaking starts on 2/09/17, it'll remove the second row's data. 

 

Ultimately my end goal is to be able to sum up the total number of days for different activities. So Kayaking should be 2 days and Running one day. If the two of the same activity collides with each other, it wouldn't be counted. Any other solutions to do it out is fine too!

 

 

So desired output would be :

ActivityStart_DateEnd_DateTotal Days
Kayaking1/09/172/09/172
Running3/09/173/09/171

 

Thanks!

 

 

Alteryx Certified Partner
Alteryx Certified Partner
Is there also a condition of overlapping dates where the start date is between the original dates and goes over the end?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

fangyi189
Asteroid

Yes, the start date may be between the original dates and goes over the end.

Alteryx Certified Partner
Alteryx Certified Partner
You will need to convert the date text into a date Field. Try using the date time tool.

Summarize the data and group by activity. Create the MIN start and the MAX end dates.

Use a formula to calculate the datetimediff(end,start,’days’)

I’m not near a computer to show you in Alteryx. The tick or quote marks need to be replaced. My iPhone uses non-standard curly quotes.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

danrh
Bolide

In case the two activities don't overlap, here's a workflow that could work:

image.png

I'm generating every day between every record's start and end date.  The Unique then ensures that each day is only counted once (if you have overlap), then summarized with a count.

fangyi189
Asteroid

Hi,

 

Thanks for getting back. I'm facing this error where "The value did not change after the loop expression. Do you mind taking a look at my configuration? 

 

Thanks!