Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
jblomqvist
Valued Contributor

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

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:

CustomerIDCustomerName
1Coca Cola
2Nike
3Apple
4Microsoft
5Dell

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?

7 Replies
Employee
Employee

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

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]

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;

Employee
Employee

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

PFA

jblomqvist
Valued Contributor

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

Hi Sunny,

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

Employee
Employee

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

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]

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]

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