Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
May be this:
LOAD OrderID,
Round(Rand() * (200+700) -700, 0.01) as Value
FROM Source;
Assuming you meant no less than -700
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 ?
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);
For your specific table, you can do this:
LOAD OrderID,
Round(Rand() * (200+200) -200, 0.01) as Value
FROM Source;
Hi Sunny,
Thanks for the reply.
How can I do it so the range is no less than 700 and no higher than 200?
May be this:
LOAD OrderID,
Round(Rand() * (200+700) -700, 0.01) as Value
FROM Source;
Assuming you meant no less than -700
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);
Thank you buddy