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

[jblomqvist]

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?

[swuehl · Accepted solution]

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]

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]

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.

[jblomqvist]

Yes you are right, I missed that!