Removing similar rows from data set - to result in one clean record

[rachaelkertes]

Hey Alteryx pros - wondering how you would approach this scenario...

I have used Ticketmaster's open API to pull a list of upcoming events in my city, to use to visualise a calendar of events in my city. 

The API is great, however it outputs a lot of duplicate line items for events depending on the type of ticket. For example, the MotoGP has over 20 rows of data for all the different sections you can buy tickets for. I would like only 1 row for MotoGP on my calendar. This happens frequently, as lots of events have various ticket options. 

How would you approach cleaning up this data set? See screenshots for examples of data and to show duplicate events with different names (same venue & date fields)

[shancmiralles]

hi @rachaelkertes ! try the unique tool and filter all columns..

Hope this helps and please mark as resolved !

[ChrisTX]

Since the Event Name is not exactly the same, using the Unique tool on that field won't work.

Try using the Formula tool to create a new field, maybe using the first 10 or 11 characters of the Event Name, like is:

  Left(Event Name],11)

Then use the Unique tool and select most fields, including the new field, but excluding the original Event Name field.

I would encourage you to check out the one-tool example for every tool under the Preparation category.  Click each tool once then click Open Example.

Chris

[shancmiralles]

yeah.. missed that part.. (not really a fan of formula lol ) 


I do my trimming in selected tool... so i'll just lower the size of the field from there..  (size 10..?) then pull the unique tool for all columns .. 

[ChrisTX]

@shancmiralles the Formula tool is an indispensable tool.  You will definitely have to learn formulas sooner or later.  For the non-programmers, I can understand the hesitancy to write "code".  But sometimes you just have to.  If you have a date and need the first day of the next month, you'll have to use a DateTime function.

When I started, I took the time to review every single function on this page: Functions (alteryx.com)

Using a Select tool to reduce a field size can result in a Conversion Error, and you lose the data in your original field.  Definitely would not recommend that approach.

Chris

[shancmiralles]

@ChrisTX  yeah .. i mean.. i do use formula tool.. but not just off the bat when resolving a workflow.. my problem as a visual learner.. but yeah.. with date and time .. definite formula tool :)