Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jblomqvist
Not applicable

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

Hi all,

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

OrderIDValue
133.44
288.99
355.99
4-157.08
545.87
6-34.99

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

May be this:

LOAD OrderID,

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

FROM Source;

Assuming you meant no less than -700

7 Replies
sunny_talwar
Not applicable

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

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
Not applicable

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

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
Not applicable

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

For your specific table, you can do this:

LOAD OrderID,

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

FROM Source;

jblomqvist
Not applicable

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

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
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Thank you buddy