topic Re: Random data selector in QlikView

Random data selector

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Random data selector

johnw — Tue, 18 Oct 2016 00:00:03 GMT

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

Re: Random data selector

Anonymous — Wed, 19 Oct 2016 08:43:40 GMT

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

Re: Random data selector

johnw — Wed, 19 Oct 2016 16:59:13 GMT

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)

Re: Random data selector

Anonymous — Tue, 25 Oct 2016 13:27:02 GMT

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