4 Replies Latest reply: Oct 6, 2016 4:58 AM by Solomon Jeyaseelan

# How to reload 10% of the Total records from a table randomly?

Hello,

I have no idea how to reload 10% of the total records randomly.

For ex:

I have a Table which has 1000 records, and I  want to load just 100(10%) records randomly.

I tried 'sample 0.1' command , but it doesn't load just 100(10%) records for me. sometimes 9%, and sometimes 11%, maybe more or less.

Does anybody have a solution for resolving this ?

Thanks a lot !

• ###### Re: How to reload 10% of the Total records from a table randomly?

If you need an exact number of random sample records, maybe you can use something along these lines:

Let vNumRecords = 1000;

Let vNumSamples = 100;

// Create Sample Set of Recno's

WHERE NOT EXISTS(SampleRecno) and Rowno() < \$(vNumSamples);

AutoGenerate 1

While iterno() <=\$(vNumRecords);

//Create test data

TestData:

Recno() as CustomerID

AutoGenerate \$(vNumRecords);

//Filter sample data set

Sample:

CustomerID

RESIDENT TestData

WHERE EXISTS(SampleRecno, CustomerID);

• ###### Re: How to reload 10% of the Total records from a table randomly?

thank u so much!   I can now get the exact number of records .

• ###### Re: How to reload 10% of the Total records from a table randomly?

Just ensure in the SampleRecno generation that you are generating much more records (iterno() <= \$(vNumRecords) than you want to receive as sample records, or in other words, vNumRecords should be much larger than vNumSamples.

That's due to the brute force algorithm to produce the fixed number of random sample values.

• ###### Re: How to reload 10% of the Total records from a table randomly?

let Tot_No_records= 20;
let percentage = 0.5 ;
No_record :
load ceil(rand()*\$(v.a)) as samp AutoGenerate 1

while IterNo() <= \$(v.a) * \$(p) ;

tab1:
rowno() as samp,

ID

FROM

[C:\ Sample.xlsx]
(
ooxml

, embedded

labels,

table

is Sheet1);