topic Re: How can I generate Random negative and Positive values for a field? in QlikView

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

jblomqvist — Thu, 14 Apr 2016 14:37:28 GMT

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?

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

sunny_talwar — Thu, 14 Apr 2016 14:41:25 GMT

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 ?

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

evan_kurowski — Thu, 14 Apr 2016 14:42:19 GMT

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);

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

sunny_talwar — Thu, 14 Apr 2016 14:44:37 GMT

For your specific table, you can do this:

LOAD OrderID,

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

FROM Source;

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

jblomqvist — Thu, 14 Apr 2016 14:49:15 GMT

Hi Sunny,

Thanks for the reply.

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

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

sunny_talwar — Thu, 14 Apr 2016 14:51:18 GMT

May be this:

LOAD OrderID,

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

FROM Source;

Assuming you meant no less than -700

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

evan_kurowski — Thu, 14 Apr 2016 14:59:37 GMT

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);

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

jblomqvist — Thu, 14 Apr 2016 15:08:43 GMT

Thank you buddy