Top 5% using Sample tool

[Hezabel]

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?

[jacob_kahn]

@Hezabel

Thats a good question....

Maybe you'll find the answer in this documentation:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Sample/ta-p/36758

Be safe,

J

[Hannah_Lissaman · Accepted solution]

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.

You should be able to adapt this approach for whatever rules you want to apply.

[Hezabel]

Thanks came to similar conclusion - just wanted to check I wasn't doing an unnecessary workaround!