topic Re: Replacing Data in QlikView

Replacing Data

shahafei2 — Mon, 17 Dec 2018 07:37:57 GMT

Hi all,

Can someone help me to find how to replace data

based on random data from other table in order to try to understand the impact of the forecast?

I succeeded to generate Orders and order lines based on our forecast, and to generate fictive Products based on our category growth forecast

The next step is to replace the ProductID (Real) with random ProductID (Real and fictive) from the same Category

suggestions?

I have two tables:

Orders:

OrderID

Date

ProductID

Qty

Products:

ProductID

ProductDesc

Re: Replacing Data

Colin-Albert — Mon, 17 Dec 2018 12:12:52 GMT

In the script editor there is an option on the Insert menu - "Test script" - this adds a script that generates 1000 rows of random data.
You can change the "autogenerate 1000" row to set how many rows are created, and the "pick(ceil(N*rand1)..." rows select 1 of N values of test data for each row.
You can modify these functions to create random data that is relevant to you.

// Test Script
Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

ASCII:
Load
if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

Transactions:
Load
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;

Comment Field Dim1 With "This is a field comment";

Re: Replacing Data

shahafei2 — Mon, 17 Dec 2018 12:31:13 GMT

Dear Colin,

Thank you,

As i wrote i already successes to "generate/duplicate Data" based on our forecast parameters(i generated fictive products base on growth coefficient per category, and "duplicated" random lines for each category per day based on their growth coefficients)

the only problem is that i would like to replace the "Original ProductID" with random "ProductID"(from list)

From my understand i should write a loop that will use pick+random function on the products tables (ProductID field)

with "where Category=X" condition

(X= will be change based on the "Original ProductID" in the line for each run)

But for some reason i did not success to pick random ProductID with the same Category as the original