How to select a random data?

Hi, I'm not sure if this is the best way, but it's a way:

DEFINE VARIABLE r1 AS INTEGER NO-UNDO.
DEFINE VARIABLE r2 AS INTEGER NO-UNDO.
DEFINE VARIABLE r3 AS INTEGER NO-UNDO.

ASSIGN r1 = RANDOM(1, 50).
DO WHILE r1 >= r2:
ASSIGN r2 = RANDOM(1, 50).
ASSIGN r1 = MIN(r1, r2).
END.

FOR EACH tablename:
ASSIGN r3 = RANDOM(1, 10000).
IF r3 > r1 THEN
IF r3 < r2 THEN
LEAVE.
END.

DISP tablename.
 
Maybe you can tell us what you want?
An example or description of what you want will help, otherwise you get answers like: find first <table>. :awink:

Casper.
 
well, it was kind of a joke, like it being as arbitrary as random...


casper.
 
1) RANDOM function to get a random number
2) Query + REPOSITION q TO ROW with this random number (no-error in case of out-of-bound)
 
This will work, but performance will be poor for large tables.

Code: 
DEFINE TEMP-TABLE ttrandom NO-UNDO
    FIELD seq AS INTEGER
    FIELD rrowid AS ROWID
    INDEX idx1 seq.
DEFINE VARIABLE iseq AS INTEGER    NO-UNDO.
DEFINE VARIABLE irandom AS INTEGER    NO-UNDO.
 
FOR EACH tablename NO-LOCK:
    CREATE ttrandom.
    ASSIGN iseq = iseq + 1
           ttrandom.seq = iseq
        ttrandom.rrowid = ROWID (tablename).
END.
 
DO irandom = 1 TO 10:
    FIND ttrandom WHERE ttrandom.seq = RANDOM (1,iseq) NO-LOCK NO-ERROR.
 
    IF AVAILABLE ttrandom THEN DO:
        FIND tablename WHERE ROWID (tablename) = ttrandom.rrowid NO-LOCK NO-ERROR.
    END.
END.
 
You don't give us much to go on, but you could build a temp-table with the keys and a sequential number and then generate a random number based on the range of those numbers. Needless to say, this is inefficient if it is a large table and you only want one record ... for which there isn't really any good method.

Perhaps it would be useful for you to tell us a bit about why you want a random record. There are a lot of cases where people think that is what they should want which are better served by some other function.
 
we will update all physical inventory each half a year.so i want select some random items to check whether these inventory is true.
thanks shpipp & tamhas 's idea, good idea. don't worry, I thinks it's ok,just less than 20k items need be checked.
 
You could calculate the highest possible RECID for the storage area containing the table and then generate random integers in that range and use them to attempt to FIND ... NO-ERROR a matching RECID. Depending on the density of records you might have a lot of failures but that shouldn't really matter for this purpose.

I use this technique to sample the distribution of record sizes in a table.
 
You may also like to look at the -rand parameter, so regardless of implementation your repeated checks don't check the same records.

Not having had much use for the RANDOM function, I don't know how restrictive its limitations are in your case, but be aware there are degrees of randomness in the ABL.
 
Ah, might I suggest that you don't actually want random selection in a case like this? I am reading what you said as doing a full end to end inventory every six months while at some unspecified interval in between you want to make some spot checks. Suppose for the sake of discussion that your spot checks are once a month. So, on month 1 you check item XYZ and then on month 2, since you are doing a random selection, XYZ might come up again while some other item might go months and months without being checked.

There is an art to cycle counting. One of the drivers is often the warehouse setup, i.e., it can be easier to count everything on one set of racks and then rotate which racks are checked each month. But, the best strategy is to do something purposive. Best I have seen and done is to do a classification using one of the ABC types of classification which figures in the rate of change and the value of the item. I.e., A is all of the items which have the most value moving through the inventory. Actually, Gordon Graham recommends classification into 13 levels ... you might look up his books. The point being that cycle counting the stuff in A is way more important than counting the stuff in the bottom category (often called X regardless of how many levels). In fact, there may be so little value and movement in the X category that you don't count it at all until the next full sweep.

The other consideration which is often thrown in here is stock levels. If there is high turn over and stock levels can get low prior to being replenished, e.g., if it is a publisher and one needs to wait for a new print run or it is simply an item with a long lead time, then it is good to count it when the level gets low. Good, both because it gets you an accurate level when you don't have many left and because there aren't as many to count.

This means setting up a system in which you record the date of the last count and come up with a selection mechanism based on classification, stock level, and time since last count. That's more work than making a random selection, but it will be far better for the business.
 
The default random number generator will produce the same sequence of psuedo-random numbers every time a session is started.

This can be either or good or bad depending on how you wish to use those numbers.

You can change the algorithm to one which randomly seeds the sequence by using "-rand 2" in your session startup parameters.

(Just take a peek at the documentation...)
 
You can always throw away a number of entries before you start using RANDOM in anger.

Code: 
DEFINE VARIABLE irandloop AS INTEGER    NO-UNDO.
DO  irandloop = 1 TO (TIME * INT (STRING (TODAY,"999999")) MOD 100000):
    RANDOM (0,irandloop).
END.

That should mean that you start on a fairly random number for each session.

Since RANDOM can be used with different number ranges when it run, the fact that the sequence is always the same shouldn't have much of an impact on programs.

It all depends what it is used for.
 
I've known what -rand means, but how i can choose more than one records once time? for example. choose 5 nums in 1 to 100.
do i = 1 to 5:
random(1,100).
end.
Click to expand...
like this, it often choose some same records.
 
You have clearly not understood that there are two distinct ways in which random() operates and that they are controlled by -rand. The default setting of -rand results in what you describe, the sequence of random numbers will be repeated. The -rand 2 setting results in a different sequence of random numbers for every session.

That point has already been explained several times in this thread so repeating it again probably isn't helpful.

You might want to try spending a couple of minutes reading the documentation regarding -rand. The documentation is the official word of Progress Software Corporation -- perhaps that will be more convincing to you.

You could also actually try the "throw away some numbers" code if you don't feel like changing the default setting for -rand. It's crude but effective. Of course if you don't try it you'll never know.

If you need someone to complete your coding assignment you're going to have to provide a much better requirements document.
 
I have set -rand 2, but same records are chosen. -rand 2 means it'll use diffirent seed each time. for example, I want choose 5 num in 1 to 10, the result is 1,3,5,3,2.
each time the seed will be diffirent. but "3" was chosen twice.
 
You're randomly choosing 5 numbers from a group of 10. Of course some numbers will be chosen more than once. It is obvious in this case because the range is so small compared to the number of selections that you are making.

If you don't want replacement to happen then you will need to keep track of the numbers that have already been used and not re-use them. A temp-table would be a flexible method for implementing this.

Have you reflected on Tamhas's comments regarding your sampling process? You may have a technical solution that does not meet the actual business need.
 
You must log in or register to reply here.
Back
Top