Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Random data selector

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

4 Replies
johnw
Champion III
Champion III

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
Not applicable
Author

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
Champion III
Champion III

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
Not applicable
Author

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