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

[jblomqvist]

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?

[Clever_Anjos]

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]

PFA

[jblomqvist]

Hi Sunny,

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

[Clever_Anjos]

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