Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 !

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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);

View solution in original post

4 Replies
swuehl
MVP
MVP

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);

Not applicable
Author

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

swuehl
MVP
MVP

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
Contributor III
Contributor III

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