Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Highlighted
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 !

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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

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

4 Replies
MVP
MVP

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

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

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 .

MVP
MVP

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

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

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

ID

  FROM



(
ooxml

, embedded

labels,

table

is Sheet1);

Please check this.

Thanks