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

Removing duplicates

fangyi189
8 - 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!

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
fangyi189
8 - 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!

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
fangyi189
8 - Asteroid

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

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

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
8 - 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!

Labels