Random Sample of Data

[#1]

Hello All,

I'm trying to figure how to show a random sample of data that has already been loaded into a QlikView application. Since we use QlikView Server/Publisher, our end users will not be able to reload the data, so the 'SAMPLE' command will not help me at all. I am loading about 50,000 records which I am displaying in a table box. I would like the user to be able to click a button that would narrow that list down to 25 random records that will still be displayed in the same table box. Can someone point me in the right direction?

I thank you in advance for your help!

[#2]

You could add a randomly generated number field to your dataset. QlikView includes the Rand() function. Add Rand() As RandomField to your dataset. Then on your button, create an Select in Field action of RandomField with the expression:

='>=$(=Max(Random, 25))'

The random number would remain the same until the data is reloaded, so you would get the same 25 records in between reloads.

You could also create a Straight Table chart and use Sort by Expression to sort by: =Rand(). Then under the Presentation tab, limit the chart to Max Number 25. I'm not sure how random this method will be either. You would probably need two charts, one for when the Random button is clicked and one for the whole data set.

If both of those methods fail, you may need to create a macro. In VB, you should be able to create an array of random numbers which could then be used to select on one of your fields to get your 25 random records. The benefit here would be that you could probably get as close to truly random as possible (random enough). I would first see if one of the other methods works well enough before trying to create a macro.

[#3]

NMiller,

Thanks for your reply, but the end user may need to generate a random sample multiple times in a given day. As it is set up right now, our data is only reloaded once per day, so the first two options you gave me won't work. I guess I'll need to create a macro.

Thanks again for your help!

[#4]

I can point you... not sure if this is the right direction though...

You can select a random sample of any field. Ideally, pick a field that has a different value on each row in your tablebox. Then, create a listbox for that field (or you can use the pulldown for the field from a multibox). With that listbox or pulldown from the multibox activated, type this expression:

=rand( )<=0.10

That will select a random sample of approximately ten percent of the values of your field. Try it out. But, I'm not sure if it will consistently give you a different set of values each time you use it during the day.

[#5]

Tim,

Thanks for your reply but the solution you suggested does not give me a different set of values with each use. I'm also trying to make this easy for the end users so typing the above formula, even though it isn't very complex, is something that I would like avoid.

[#6 · Accepted solution]

The number of times your users will need to do this selection on the exact same set of data is probably limited. But, you can make the expression do a different selection each time. Type this:

=rand( )<0.10+0*now()

Here's an idea that will make it easy for your report users: after you do this process once on your document you can save the selection as a bookmark. QlikView will save the expression and not just a static list of values. Now, whenever you click the bookmark it will do a selection of a random ten percent of available values. You can export the bookmark, of course, and share it. This idea works best if the special selection is the only selection in your document so that the bookmark contains only the selection for that one field.

Remember that a bookmark can be made an action in QlikView v9. So, you can make the bookmark action execute as a button or based on some other trigger. You might want to make the button do two actions: first a clear on the field and then application of the bookmark.

[#7]

Tim,

That did the trick. Thanks so much for your help!

[#8]

Hi Tim and hi everyone,

I just tried to integrate your expression in my data model and I fiinally get different values every time I define my sample size new. However, due to the "now()"-part the values keep changing by itself constantly.

This is the expression in my text object which is displaying my sample.

=Concat(If( Aggr(Rank(Rand()) <= vSampleSize + 0* now() , Änderungsbelegnr), Änderungsbelegnr, Null()), ', ')

(Thanks to Richard Cook.)

Any ideas on how to fix this?

[evan_kurowski]

add the argument to the Now() function that doesn't continuously call it.

0 (time at last data load)
1( time at function call)
2 (time at app was opened)

Put Now(1) on your UI, and it will tick seconds away like a timer. 

(but watch out for community, which can be a sequential liar) 😉