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

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

jblomqvist — Fri, 15 Apr 2016 11:01:21 GMT

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?

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

swuehl — Fri, 15 Apr 2016 11:04:50 GMT

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

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

jblomqvist — Fri, 15 Apr 2016 11:43:15 GMT

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.

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

Gysbert_Wassenaar — Fri, 15 Apr 2016 11:48:36 GMT

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.

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

jblomqvist — Fri, 15 Apr 2016 12:24:50 GMT

Yes you are right, I missed that!