How to select random records to load into qvw?

[#1]

Hi,

I have records in two sheets in excel file.

Sheet A have records 20,000 from jan 2011 to June 2012 and sheet B have records 10,000 from  july 2012 to current  which is sorted based on date in both sheets.

I created qvw. How can I select to reload the data into qvw with selection of random records in both sheets without depending on sorted date?

Could anyone suggest me please.

Thanks.

[daveamz]

Hello,

You can use something like:

LOAD * FROM .... WHERE Rand() > 0.5;

Regards,

David

[rbecher]

We can do this easily with the rand() function as shown in this load script example:

// in this case we want to load a 10% sampling set of the data

data:

LOAD ….<your fields>…

FROM <your QVD file> // or RESIDENT

WHERE ceil(rand() * 100) <= 10;

This is probably slower than SAMPLE but with SAMPLE the whole data set is loaded into memory first which could be a problem if you have large data sets (Big Data etc.).

I have included this kind of sampling in my TIQView data profiling application:

QlikView Application: TIQViewQVD data profiling

- Ralf

[ashfaq_haseeb]

Ralf Becher

I cannot access your data profiling application. It says unauthorized access.

can you attach it here or give me access.

Regards

ASHFAQ

[rbecher]

Hi Ashfaq,

you have to join the Data Quality group: Data Quality

- Ralf

[#6]

Thanks. I tried this it is picking randomly. would this change every time ? because I tried with

SAMPLE 0.25 Load *

it is showing on count. Again after some time I tried SAMPLE 0.25 it is showing different count for the same sample load. Could you suggest me this please.

[Colin-Albert]

Sample will created a new data sample each time it is loaded.

Save the initial sample load data to QVD and then use the qvd for subsequent testing.

You could use BUFFER for testing, just make sure you remove the buffer before going live!

buffer(stale after 30 days)

sample 0.25 load *

[#8]

Amelia,

Look at my previous answers

Reg

D

02-06-2014 16:08 użytkownik "Amelia D" <qcwebmaster@qlik.com> napisał:

Qlik Community <http://community.qlik.com/> How to select random

records to load into qvw?

reply from Amelia D

<http://community.qlik.com/people/Amelia2000?et=watches.email.thread> in *New

to QlikView* - View the full discussion

<http://community.qlik.com/message/539868?et=watches.email.thread#539868>

[#9]

Hi,

You could try:

// TableName contains Values for bootstarp

Nsample = 10000; // NoOfRows('TableName');

LOAD

peek(Values,round(Rand()*$(Nsample)),'TableName') as Rand 

AutoGenerate $(Nsample);