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 to create Random Values in an existing table with numerical, text, and null values in a single field?

Hi all,

How to create Random Values in an existing table with numerical, text, and null values in a single field?

For example:

Create random OrderID's starting from 2001 to 190000, some of these must be null and some should say "No Order ID". This would all be in an OrderID field.

I already have a table I just have to create the above in there. Any idea how to do this?

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: How to create Random Values in an existing table with numerical, text, and null values in a single field?

Maybe like

LOAD *,

          if(Random <0.1,NULL(), if(Random > 0.9,'No Order ID', floor(RAND()*187999)+2001)) as OrdeID;

LOAD

     RAND() as Random,

     OtherField

FROM ...;

4 Replies
swuehl
Not applicable

Re: How to create Random Values in an existing table with numerical, text, and null values in a single field?

Maybe like

LOAD *,

          if(Random <0.1,NULL(), if(Random > 0.9,'No Order ID', floor(RAND()*187999)+2001)) as OrdeID;

LOAD

     RAND() as Random,

     OtherField

FROM ...;

jblomqvist
Not applicable

Re: How to create Random Values in an existing table with numerical, text, and null values in a single field?

Hi swuehl,

Thanks for the reply.

The only thing this doesn't randomly generate is some null values. Any idea how to alter it to produce some null values? The other two requirements are met.

Gysbert_Wassenaar
Not applicable

Re: How to create Random Values in an existing table with numerical, text, and null values in a single field?

It does generate about 10% nulls. Why do you think it doesn't? Because you don't see nulls in a listbox? That's because null isn't a value so it won't show up in a listbox object.


talk is cheap, supply exceeds demand
jblomqvist
Not applicable

Re: How to create Random Values in an existing table with numerical, text, and null values in a single field?

Yes you are right, I missed that!