How can i auto generate data via load script?

[seWork]

I would like to auto-generate new records via the load script based on existing data

My DB contains the following tables:

Item_Master_Data:

SKU,

SKU_Desc,

Category,

Quantity_Units_In_Box,

Quantity_Boxes_In_Pallet,

Quantity_Units_In_Pallet

Inventory:

Inv_Date,

SKU,

Batch,

Pallets

Orders:

Consingment_ID,

Order_ID,

Customer_ID,

Order_Creation_Date,

SKU,

Requierd_QTY,

Supplied_QTY,

Order_Supply_Date

Items_Growth:

Category,

Count(Distinct SKU)

Category_growth_factor

I created an input field that the user is able to write into and to set the growth factor for each category

then I allow the user to export the growth table into a qvd file and to reload this data

The new data is loaded into the following table:

Items_growth_2:

Category,

User_category_growth_factor

ceil(Count(Distinct SKU)* [User_category_growth_factor]) - Count(Distinct SKU) as [Future SKUs]

Then I should create a table to calc the Num fields avg and to pick a random value for text fields from existing data

//I didn't find yet how to implement the pick random functionality, I understand that for the avg I can use

Where isnum($(fname))

in the following script(as part of the inner join)?

avgitemtable :

load distinct Category  resident Item_Master_Data;

for i = 2 to vTable

    let fname = FieldName(i,'Item_Master_Data');

    inner join (avgitemtable)

        Load Category, CEIL(Avg( $(fname) )) as AVG.$(fname)

        Resident Item_Master_Data group by Category;

next i;

load distinct Category  resident Item_Master_Data;

for i = 2 to vTable

    let fname = FieldName(i,'Item_Master_Data');

    inner join (avgitemtable)

        Load Category, CEIL(Avg( $(fname) )) as AVG.$(fname)

        Resident Item_Master_Data group by Category;

next i;

the next stage is to autogenerate the new SKU as many as the user set in the growth factor per category

I tried to use the following script for that but without success

let vCategory = count(DISTINCT 'Category');

for x = 1 to vCategory

let vFSKUs = '[Future SKUs]';

TempItemsSet:

Load

Category,

SKU&RecNo as SKU AutoGenerate $(vFSKUs);

next x

Thank you

[dwforest]

Something like:

let vSKU= count(DISTINCT  SKU);

for x = 1 to vSKU

TempItemsSet:

Load

Category,

SKU + IterNo() as SKU AutoGenerate [Future SKUs]

While SKU = vSKU;

next x

[seWork · Accepted solution]

Map1:

mapping Load Distinct Cat_ID,Future_SKUs Resident Items_growing_2;

let vCategory = FieldValuecount(FieldName(1,'Items_growing_2')); 

for i = 1 to vCategory

let vGrowthCat = peek('Future_SKUs',$(i)-1,'Items_growing_2');

TempItemsSet:

Load

$(i) as Cat_ID,

'FI-'&recno()*1000&$(i) as SKU

AutoGenerate $(vGrowthCat);

next i

left join(TempItemsSet)

LOAD RowNo() as Cat_ID,

Category

Resident Items_growing_2;

let vAvgTable = NoOfFields('avgitemtable');

for iav = 2 to vAvgTable

    let Avgfname = FieldName(iav,'avgitemtable');

Let Avgfname2= mid(Avgfname,5,len(Avgfname)-4);

left join (TempItemsSet)

Load Category,

$(Avgfname) as $(Avgfname2)

Resident avgitemtable;

next iav

Drop Table avgitemtable;

Drop Field Cat_ID from Items_growing_2;

Drop Field Cat_ID from TempItemsSet;

[seWork]

I solved it by using the above script