topic Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided] in QlikView

How can I insert random values into pre-loaded fields in a table? [Initial script provided]

jblomqvist — Tue, 31 May 2016 13:46:22 GMT

Hi all,

I have the following script:

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, Numeric, None

OrderDate, Datetime, None

CustomerID, String, None

CustomerName, String, None

DispatchDate, Date, OrderDate

];

FieldList:

LOAD Concat(

Pick(Match(DataFormat,'Numeric','Datetime','String','Date'),

'Round(100*Rand())',

'Timestamp(Makedate(2016)+Rand()*366)',

'CHR(ORD(''A'')+floor(Rand()*26))',

'Date(Makedate(2016)+floor(Rand()*366))'

) & ' AS [' & FieldName & ']', ', ') AS ColumnList RESIDENT ColumnTable;

LET vColumnList = peek('ColumnList');

DataTable:

LOAD $(vColumnList)

AUTOGENERATE 1000;

-------------------------

What I would like to do is populate the DataTable fields, OrderID, OrderDate, CustomerID, CustomerName, DispatchDate with values.

Take the example of CustomerID and CustomerName. The values for the fields should be populated from the table below:

CustomerID	CustomerName
1	Coca Cola
2	Nike
3	Apple
4	Microsoft
5	Dell

I would like to populate CustomerID in the DataTable with random CustomerID's from 1 to 5 in the table above, and CustomerName in the DataTable with CustomerName values in the table above.

How can I achieve this in the existing script please?

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

Clever_Anjos — Tue, 31 May 2016 14:01:25 GMT

Maybe this?

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, ID, None

OrderDate, Datetime, None

CustomerID, ID, None

CustomerName, Customer, None

DispatchDate, Date, OrderDate

];

set vExp= Pick(Ceil(5*rand()),'Coca Cola','Nike','Apple','Microsoft', 'Dell');

FieldList:

LOAD Concat(

Pick(Match(DataFormat,'Numeric','Datetime','String','Date','ID','Customer'),

'Round(100*Rand())',

'Timestamp(Makedate(2016)+Rand()*366)',

'CHR(ORD(''A'')+floor(Rand()*26))',

'Date(Makedate(2016)+floor(Rand()*366))',

'Ceil(5*rand())',

'$(vExp)'

) & ' AS [' & FieldName & ']', ', ') AS ColumnList RESIDENT ColumnTable;

LET vColumnList = peek('ColumnList');

DataTable:

LOAD $(vColumnList)

AUTOGENERATE 1000;

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

sunny_talwar — Tue, 31 May 2016 14:02:06 GMT

May be this:

MappingTable:

Mapping

LOAD CustomerID,

CustomerName

FROM

[https://community.qlik.com/thread/219013]

(html, codepage is 1252, embedded labels, table is @1);

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, Numeric, None

OrderDate, Datetime, None

CustomerID, Numeric1, None

DispatchDate, Date, OrderDate

];

FieldList:

LOAD Concat(

Pick(Match(DataFormat,'Numeric', 'Numeric1', 'Datetime','String','Date'),

'Round(100*Rand())',

'Round(Rand() * (5-1)) + 1',

'Timestamp(Makedate(2016)+Rand()*366)',

'CHR(ORD(''A'')+floor(Rand()*26))',

'Date(Makedate(2016)+floor(Rand()*366))'

) & ' AS [' & FieldName & ']', ', ') AS ColumnList

Resident ColumnTable;

LET vColumnList = peek('ColumnList');

DataTable:

LOAD *,

ApplyMap('MappingTable', CustomerID) as CustomerName;

LOAD $(vColumnList)

AUTOGENERATE 1000;

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

Clever_Anjos — Tue, 31 May 2016 14:02:07 GMT

PFA

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

jblomqvist — Tue, 31 May 2016 14:21:00 GMT

Hi Sunny,

What is the Purpose of Numeric1 field please? Is that essentially the CustomerID?

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

Clever_Anjos — Tue, 31 May 2016 14:22:25 GMT

To use different expressions for different kind of fields

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

sunny_talwar — Tue, 31 May 2016 14:24:38 GMT

Numeric1 was used to differentiate it from the other Numeric. I wanted to create a new Random number between 1 and 5 and use that to map the CustomerName to it.

Re: How can I insert random values into pre-loaded fields in a table? [Initial script provided]

MarcoWedel — Tue, 31 May 2016 20:53:34 GMT

Hi,

please close this thread Interesting challenge: How to create a real table from a list of field names?

by selecting a correct answer.

thanks

regards

Marco