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

[#1]

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 !

[swuehl · Accepted solution]

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

LOAD SampleRecno

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

LOAD Ceil(Rand()*$(vNumRecords)) as SampleRecno

AutoGenerate 1

While iterno() <=$(vNumRecords);

//Create test data

TestData:

LOAD Keepchar(Hash256(RAND()),'ABCDEFG') as Customer,

  Recno() as CustomerID

AutoGenerate $(vNumRecords);

//Filter sample data set

Sample:

LOAD Customer as CustomerSample,

  CustomerID

RESIDENT TestData

WHERE EXISTS(SampleRecno, CustomerID);

[#3]

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

[swuehl]

Glad this resolved your issue.

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.

[soloeeeoff]

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:
left keep LOAD
rowno() as samp,

ID

  FROM

(ooxml

, embedded

labels,

table

is Sheet1);

Please check this.

Thanks