Skip to Content

How to randomly select records in Data Services

Oct 27, 2017 at 08:01 PM


avatar image

I have a flat file containing X records. I want to randomly select Y records from this file to a separate flat file. Is there a simple way to do this in Data Services? Note, this should be a true random selection, not every "nth" record where n = x / y.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Dirk Venken
Oct 28, 2017 at 10:18 AM

2 Query transforms:

  1. Add a column RANDOM_NUMBER of type real or double, map it to rand() -- built-in function that generates random number between 0 and 1
  2. Add a where-clause: e.g. RANDOM_NUMBER < 0.2 -- will select 20% of your records
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks for the response, but unless I'm misunderstanding, this does not achieve the desired result. Since the rand() function assigns randomly to each row, there is nothing to ensure that values between 0 and 1 will be distributed evenly throughout the input population X. So this technique results in a random number of records being selected, and not 20% of the input population.

I attempted using the steps outlined, and in 10 trials, the output yielded various results ranging from 30% to 45% of input.


You're right and you're not.

Random values are by nature distributed evenly throughout a population. The bigger the input size, the more exact the output.

Unfortunately, the DS rand() function does not seem to generate real random numbers. I wasn't aware of that issue. Use rand_ext (without a seed) instead. That will do the trick.