Top 5% using Sample tool

I am breaking out a dataset into products, then need to find the value that the top 5% and 2% are above. Sounds simple! I have used a descending sort, then sample top 5% records, then sample last record - which works great. However some of my products break out with very few records - and these bring back nothing. eg. one product brings back 3 records and the result brings back nothing, so top 5% would equate to 0.15 of a record. however with 30 records which would be 1.5 of a record brings back 2 records. So my question is does this tool round to the nearest whole record or something?

How can I get around this? Assuming the above is correct - could use an if function to split out if record number < 25 (for 2%) or 10 (for 5%) calc then just use the top record?

Datasets

Preparation

Accepted answers

Hannah_Lissaman

Hi @Hezabel

If X% of your number of rows is less than 0.5, then it will return no records. E.g. if you sample the top 5% of 9 records, it will return 0 rows. If you sample 5% of 10 records, it returns 1 row because 5% of 10 is exactly 0.5.

You can build a workflow which checks the number of rows, and passes your data through different sample tools depending on which was chosen.

I used a Formula tool with a simple if statement to check which approach I wanted it to use. The Append tool adds this flag to every row of my data, so when I then Filter it will send all data down the same path. The sample workflow is attached.