Creating Random numbers within the report

[VincentCWS]

Hi,

Tried to look for random function but am not able to find anything that I can use. Does anybody have any suggestion ?

What I am trying to do is to bring in say 1000 customer numbers and only want to choose 100 of those customers by randomising them.

 

[blom0344]

I can not think of a TRULY random selection within reporter (can think of pseudo-random strategies, but will discuss them later)
Depending on the database type you work with there are functions available that create more or less random numbers within the range of 0 and 1.
For instance in DB2 there is the rand() function and this is a piece of freehand SQL to get a random set of customers out of a customer-table:

Select CCUST,CNME from
(SELECT X.*,
ROW_NUMBER() OVER (ORDER BY RAND()) AS COUNTER
FROM TARGET.BIKE_CUSTOMER X) AS TEMP
WHERE COUNTER <= 100
ORDER BY CCUST

This will select 100 random customernumbers

If you want to work in the conditions for the initial selection (that got the 1000 customers) you will get something like this:

Select CCUST,CNME from
(SELECT X.*,
ROW_NUMBER() OVER (ORDER BY RAND()) AS COUNTER
FROM
(SELECT CCUST,CNME FROM TARGET.BIKE_CUSTOMER WHERE CCOUN = 'NL') X) AS TEMP
WHERE COUNTER <= 100
ORDER BY CCUST

So , if selecting customers with condition CCOUN = 'NL' would fetch 1000 customers , this SQL will pick 100 from them.
(other databases will have slightly different SQL functions for this to work)

A pseudo approach at report level would be something like generating an ASCII print of a customername (see the FAQ I wrote on comparing lists in the FAQ folder of the forum)
You van then pick the top 100 from this variable using the ranking at report level


T. Blom
Information analyst
tbl@shimano-eu.com

 

[VincentCWS]

Thanks Blom.Had a look at the FAQ and am actually not allowed access to free hand sql, therefore have to find ways to do random using the functions available.

 

[Sridharan]

A Quick Thought! may be you can add on this.. To display 100 records in the reporter create a variable say

LN as

=RowIndex() + 1

Apply the filter on this and set the condition to LN < 100 to display the first 100 Records Fetched by DP.

Note this is not a Random one and displays the first 100 records fetched by DP in order.

Sri

 

[VincentCWS]

Thanks for that Sri.

 

[blom0344]

Vincent,

If you read through the FAQ to the end you will see that by using the ASC function you can work WITHIN the report with a variable that will generate an ASCII fingerprint.

First get rid of spaces in the names and create equal length strings:

=RightPad(Replace(<Name> ,&quot; &quot; ,&quot;&quot ,30 ,&quot;A&quot

Call this variable <name no spaces>

As example I take out first 6 ASCII codes from the string and use MOD function to create 10 groups:

=Mod(Asc(SubStr(<name no spaces> ,1 ,1))+
Asc(SubStr(<name no spaces> ,2 ,1))+
Asc(SubStr(<name no spaces> ,3 ,1))+
Asc(SubStr(<name no spaces> ,4 ,1))+
Asc(SubStr(<name no spaces> ,5 ,1))+
Asc(SubStr(<name no spaces> ,6 ,1)) ,10)

Call this variable < name ASCII mod>

You will notice that with large enough population you will now have 10 different values for < name ASCII mod >

Choose any one of the values through applying a filter and you have a random subset.

Using Rowindex() is indeed a very pseudo-random way to get a subset.

(I remember a prof mentioning that creating a real set of random numbers is mathematically very difficult)

T. Blom
Information analyst
tbl@shimano-eu.com