topic How can i auto generate data via load script? in QlikView

How can i auto generate data via load script?

seWork — Mon, 11 Jun 2018 09:21:09 GMT

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

Re: How can i auto generate data via load script?

dwforest — Mon, 11 Jun 2018 17:48:26 GMT

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

Re: How can i auto generate data via load script?

seWork — Tue, 12 Jun 2018 08:24:56 GMT

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,

Re: How can i auto generate data via load script?

seWork — Tue, 12 Jun 2018 08:25:46 GMT

I solved it by using the above script