How to select a random data?

[sphipp]

A random number generator generates random numbers and that's all it does.

Tom beat me to it, but ...

If you are generating random numbers between 1 and 10 and want 5 of them then chances are you will get duplicates.

If you want to exclude duplicated numbers then you will need to store the numbers you have already generated and ignore any repeats of those numbers. You can do this by storing the numbers in a temp-table, a character string or however you want to do it.

/* randv.i */
DEFINE TEMP-TABLE ttrandom NO-UNDO
    FIELD seq AS INTEGER
    FIELD rrowid AS ROWID
    INDEX idx1 seq.
DEFINE TEMP-TABLE ttfound NO-UNDO
    FIELD rrowid AS ROWID
    INDEX idx1 rrowid.
DEFINE VARIABLE iseq            AS INTEGER    NO-UNDO.
DEFINE VARIABLE irandom         AS INTEGER    NO-UNDO.
DEFINE VARIABLE rfound          AS ROWID      NO-UNDO.
DEFINE VARIABLE iMaxRecords     AS INTEGER    NO-UNDO.
DEFINE VARIABLE iNumberSearches AS INTEGER    NO-UNDO.
FUNCTION RandomRowid RETURNS ROWID (pii_seq AS INTEGER):
    FIND ttrandom WHERE ttrandom.seq = RANDOM (1,pii_seq) NO-LOCK NO-ERROR.
    IF AVAILABLE ttrandom THEN RETURN ttrandom.rrowid.
END FUNCTION.
UPDATE iNumberSearches LABEL "Number of Records to Find"
       iMaxRecords LABEL "Max Records in Search" 
    WITH SIDE-LABELS 1 DOWN THREE-D FRAME f1.
HIDE FRAME f1.
RUN ipRandomise.
RUN ipStoreTable.
RUN ipFindRandomRecords.
FOR EACH ttfound NO-LOCK,
    EACH {1} WHERE ROWID({1}) = ttfound.rrowid NO-LOCK:
    RUN {2}.
END.
PROCEDURE ipStoreTable:
EMPTY TEMP-TABLE ttrandom.
ASSIGN iseq = 0.
FOR EACH {1} NO-LOCK:
    CREATE ttrandom.
    ASSIGN iseq = iseq + 1
           ttrandom.seq = iseq
        ttrandom.rrowid = ROWID ({1}).
    IF iMaxRecords > 0 AND iseq >= iMaxRecords THEN LEAVE.
END.
END PROCEDURE.
PROCEDURE ipFindRandomRecords:
EMPTY TEMP-TABLE ttfound.
ASSIGN irandom = 0.
REPEAT:
    ASSIGN rfound = RandomRowid (iseq).
    FIND ttfound WHERE ttfound.rrowid = rfound NO-LOCK NO-ERROR.
    IF NOT AVAILABLE ttfound THEN DO:
        CREATE ttfound.
        ASSIGN ttfound.rrowid = rfound
               irandom        = irandom + 1.
    END.
    IF irandom >= iNumberSearches THEN LEAVE.
END.
END PROCEDURE.
PROCEDURE ipRandomise:
    DEFINE VARIABLE irandloop AS INTEGER    NO-UNDO.
    DO  irandloop = 1 TO (TIME * INT (STRING (TODAY,"999999")) MOD 100000):
        RANDOM (0,irandloop).
    END.
END PROCEDURE.

So, if you had a table "table1" with 2 fields "field1" and "field2" you could do:

{randv.i "table1" "ipDisplay"}
FORM WITH FRAME f2.
PROCEDURE ipDisplay:
DISPLAY table1.field1 table2.field2 WITH FRAME f2 DOWN.
DOWN WITH FRAME f2.
END PROCEDURE.

You could use this include file to insert random table searches in any program.

The first paramter in the include file is the table to be randomly sampled.
The second parameter in the include file is a procedure to be run for each of your sampled records.

 

[tamhas]

Not only was it chosen twice in this small run for reasons which have been explained, but think about the probabilities in successive runs. Within a single run you can avoid duplicates by tracking numbers already picked, but how are you going to do that between runs. Chances are ... such being the nature of random ... that you will not only count the same item in successive runs but that over a series of runs you will fail to count some items.

Read Graham.

 

[rainylsh]

choose 5 in 10 nums or 1000 nums, it's able to choose duplicate records. just small or large chance. I have a idea.

def i as int.
def j as int.
def k as int.
def temp-table temp1
field id as int
field rowid as rowid.
for each tablename.
i = i + 1.
create temp1.
assign
temp1.id = i
temp1.rowid = rowid(tablename).
end.
do j = 1 to 5:
k = random(1,i - j + 1)
find temp1 where temp1.id = k.
find tablename where rowid(tablename) = temp1.rowid.
delete temp1.
if k <> i - j + 1 then do:
find temp1 where temp1.id = i - j + 1.
assign temp1.id = k.
end.
end.

like this, i can choose 5 different records.

 

[tamhas]

As noted, yes, if you keep track of the numbers already selected, then you can avoid duplicates in any one run. But, unless you keep track in the database of what you counted and when, there will be nothing to keep you from duplicates. In fact, without one of the tricks to insure a new sequence each time, you would actually count the *same* set of "random" items each time!

The real issue here is not a programming trick to get random items and avoid duplicates. The real issue here is how to do meaningful cycle counting. This is not a particularly hard problem, but it does take a couple of database fields and a couple of new programs to implement. One can implement it with a couple different levels of sophistication, but frankly from simplest to most exotic there isn't more than a day or two total programming difference. It just isn't that complicated a problem.

 

[rainylsh]

"you would actually count the *same* set of "random" items each time!" I don't think so. I've set -rand 2, so each time the random I select is not the *same* one seed.

 

[tamhas]

Which is why I prefaced that statement by "without one of the tricks to insure a new sequence each time,". My point was to emphasize that a supposedly random set could actually produce something that was nonsense from a business perspective. With rand 2, it isn't that silly, but you still have the chance of duplicates and of missing some items entirely between your end to end counts. This is not good business sense.

Good business sense is to select the items for counting based on business rules such as frequency of turn, value, time since last count, convenience of physical location in the warehouse, likelihood of pilferage, etc. Good business sense is *not* random, it is purposive and systematic.