Assigning random value from reference table

[tjamal1]

Hello , 

I have reference table which contains category and their max and min value

Category	Min	Max
A	10	15
B	16	20
C	21	25

I have another table which contains user category and his ID 

I want to assign random value to him based on his category max and min value 

User	Category	Value
1	A
2	A
3	B
4	C
5	C

Value should be between max and min value of his category 

Is there anyway to do it ?

[WilliamR]

Hi @tjamal1 ,

using the RandInt function, it should do what you want.

(If this post helps, then please consider it as the solution to help the other members find it more quickly).

[tjamal1]

Thanks for the reply

can you briefly explain , whats the logic for this formula?

[danilang]

Hi @tjamal1 

You can do this by joining on the category and then using 

[Min]+randint([max]-[Min])

giving you

 

Dan

[tjamal1]

Thanks for the reply 

does it work for decimal values ? 

[WilliamR]

Yes, consider this option depending on the number of decimal you want:

RandInt([Max]*10000-[Min]*10000)/10000 + [Min]

The formula generate a random number in range of Max-Min where you add Min to get what you want.

Don't forget to modify the type of the column to double.

(If this post helps, then please consider it as the solution to help the other members find it more quickly).

[danilang]

Hi @tjamal1 

This will only add a integer value to the min.  To add a decimal in the range use

[Min]+ Rand()*([Max]-[Min])

Rand() returns a real number between 0 and 1.  Multiply this by the total range and add [min] to give your final value

Dan

[tjamal1]

Will it work for 

  Min            Max

0.6

1.3

   Min           Max

0.6

100

[danilang · Accepted solution]

Hi @tjamal1 

Yes it will

Dan