community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!

Delete matching records from multiple tables workflow

Meteoroid

Hello - I'm very new to this tool. Appreciate all the assistance.

 

I have a data set with a list of clients, engagements, type of work and revenue by year (5+ years).

 

I need to get a list of clients for each work type based on max revenue. Type X to be filtered for only 2017-2019 years for max revenue. Type Y the same and Type Z work type will be open to all years. And in that order Type X >> Type Y >> Type Z.

 

The Key is to not have a client considered again while filtering for max rev if it's already captured before. For instance: if client 123456 was captured under Type X, it should not be in the data set that will be filtered down to capture Type Y clients and not for Type Z clients if they were captured under Type Y.

 

I've assigned an excel version of the issue. Hope that explains.

Highlighted
Quasar
Quasar

There is a tool that is under utilized that is perfect for this use case. The Tile tool allows you to group by fields and then adds flags to each record based on their order in the data. If you do some pre-sorting, this will allow you to filter the way you want. See the attached workflow to further illustrate the point.

Magnetar
Magnetar

Hi @vmshah 

 

another approach is to take a Filter/Join method. You can set up your first filter to look for just Type X, in the year range. The T output are the records you want for the Type X summary. Join that back to the "F", on the Client ID field. Whatever doesn't match from the F side will be what you want to check for your next filter.

 

Rinse and repeat.

image.png

 

Let me know if this helps.

 

Cheers!

Esther

Meteoroid

Thank you both!! This was great. Tile is a great tool to know as well. I did go with the Filter and Join approach since I think that fit best with what I want to develop further off of this. Thank you again!! Much appreciated.

Labels