How can I generate Random negative and Positive values for a field?

[jblomqvist]

Hi all,

Let's say I have a following table like this:

OrderID	Value
1	33.44
2	88.99
3	55.99
4	-157.08
5	45.87
6	-34.99

How can I generate random Negative and Positive values for the Value field using the rand() function?

[sunny_talwar]

Try this may be:

Table:

LOAD Round(Rand() * (200+200) -200, 0.01) as RandValue

AutoGenerate 1000;

UPDATE: This will generate 1000 values between -200 and 200

UPDATE: Stole the idea from here -> How to create Random number within specific range in Qlikview/Qliksense in script ?

[evan_kurowski]

Hello John,

You could try something like

LOAD 100* (Rand() * sign(Rand() - .5)) AS POS_NEG AUTOGENERATE(10);

For 10 randoms between 100 to -100

then maybe assign a variable for your spread:

Let vSpread = 200;  //change bandwidth of randoms as needed

LOAD $(vSpread) * (Rand() * sign(Rand() - .5)) AS POS_NEG AUTOGENERATE(10);

[sunny_talwar]

For your specific table, you can do this:

LOAD OrderID,

          Round(Rand() * (200+200) -200, 0.01) as Value

FROM Source;

[jblomqvist]

Hi Sunny,

Thanks for the reply.

How can I do it so the range is no less than 700 and no higher than 200?

[sunny_talwar · Accepted solution]

May be this:

LOAD OrderID,

          Round(Rand() * (200+700) -700, 0.01) as Value

FROM Source;

Assuming you meant no less than -700

[evan_kurowski]

John Blomqvist wrote:

Hi Sunny,

Thanks for the reply.

How can I do it so the range is no less than 700 and no higher than 200?

In this case you're talking about a bandwidth of 900, offset by -700, assuming Sunny is correct and you mean -700>x<200

Let vSpread = 900;

Let vOffset = -700;

LOAD $(vSpread) * Rand() + $(vOffset) AS POS_NEG AUTOGENERATE(10);

[jblomqvist]

Thank you buddy