Random data selector

[Anonymous]

Hi guys.

let me set the scene for you. I am running a ticket quality survey each month, and i need qlikview to pick 5 random rows (in the attached file) for each "Creator" field for the previous month. The other conditions are that the "Creator" must be the same as the "Person Resolved" and the Status must be 'Cleared' or 'Closed'

The attached data may not contain enough info to generate the sample data - however the live data i am working with will have enough data. (I cant post live data).

please let me know if you need any further info.

Any assistance in my quest would be very much appreciated.

[johnw · Accepted solution]

Maybe something like this:

MyTable:
LOAD *
WHERE [Count] <= 5
;
LOAD some fields
,if([Creator] = peek([Creator]),peek([Count])+1,1) as [Count]
FROM your file
WHERE [Creator] = [Person Resolved]
AND match([Status],'Cleared','Closed')

AND monthstart([Create Date]) = monthstart(today(),-1)

ORDER BY [Creator]

;

Or not quite what you want, but a likely faster way to get a random selection is to use a sample load, where you give it a probability of loading each row. Something like this, I think.

MyTable:
SAMPLE 0.05
LOAD some fields
FROM your file
WHERE [Creator] = [Person Resolved]
AND match([Status],'Cleared','Closed')
AND monthstart([Create Date]) = monthstart(today(),-1)

;

edit: added previous month check

[Anonymous]

Hi John.

thanks for the code. Its useful, but not delivering the desired results.

I am getting a random selection - however, it is not giving me the 5 rows per creator. sometimes it gives me a couple and sometimes quite a few (up to 12).

Also, its not selecting the rows from the previous month.

It might be that the sample data isn't sufficient - but the fact that i was getting more than 5 of the same creator suggests otherwise?

I know my requirements are quite specific, therefore it may not be as straight forward as i thought it could be?

Thanks

[johnw]

The second bit of script would be expected to give random results. But the first bit of script gives me exactly 5 records per creator when sourcing from 5000 random records. See attached.

I hadn't noticed that you only wanted records from the previous month. I hope it's clear how to add additional qualifications to what sample data you retrieve. (edit: added to previous reply)

[Anonymous]

Hi John,

thanks once again for your response.

I have got this working, using your revised code - not sure what was going wrong when i first did it.

Thank you for your assistance.

Regards

Aaron