Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

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
Clever_Anjos
Employee
Employee

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;

sunny_talwar

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;

Clever_Anjos
Employee
Employee

PFA

jblomqvist
Specialist
Specialist
Author

Hi Sunny,

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

Clever_Anjos
Employee
Employee

To use different expressions for different kind of fields

sunny_talwar

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.

MarcoWedel

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