Remove Top and Bottom 5% of Records

[Brian_H]

Hello

I have a dataset of about 60k lines with only 3 columns of data. They data has outliers on both the high and low end of the data. Therefore, I would like to strip out the highest 5% and lowest 5% based on a specific column that is numeric. I am struggling on figuring out how to do this and am looking for any recommendations.

Thanks

Brian 

[Joe_Mako · Accepted solution]

Are you looking for something like the attached?

- Sort the column of interest

- Record ID to get an incrementing number per record

- Summary to get a total count of records

- Append to have the count on each record

- Filter to remove the top and bottom 5 percent

[Brian_H]

Hello Joe,

This is exactly what I needed. Thank you!!!

Thanks

Brian

[cavano]

Is there a way to achieve the same result but for In-Database?

[BenMoss]

There is an in-db sample tool. With which you can specify the % or records to return. You could use this twice kn your data stream to get the top 5% and then bottom 5% and then union the results together.

Ben

[BenMoss]

Alternatively, you could use a SQL statement on input to first sort and then give an incrimental ID to your data; once you have this you have all the other tools in-db that will allow you to create your solution.

Link to in-DB append which is not standard in the package: https://gallery.alteryx.com/#!app/Append-In-DB/5ab3a06f826fd310ecda0b47

Ben