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: 
rammarthi
Creator
Creator

Random data generation for sales data

Hi,

I have the following table with sales data of 200,000 rows, attached is the excel sheet.

randomdata.png

Now I want to generate random data replica for above data where each of the random variable(Random Order Number) generated is looped over the first column (Order Number Values) and the dates are pushed back by one year accordingly without loosing relation with Random Number. The result should be as below,

randomdata1.png

attached is the excel file for reference.

2 Replies
sunny_talwar

How are you hoping to create the Random Order Number? Is this completely random or is this based on some logic? The random order date and random Delivery date are always 365 days before the order date and delivery date, resp.?

rammarthi
Creator
Creator
Author

I wanted to generate a 6 digit character string of upper case for Order Number at random and as said the dates can be a year ago dates thats not a problem.

I used the following code to do this and tried to loop it on existing pattern of Order Numbers Can you let me know why the loop is not working.

//Get Max Row Number

TempToGetMaxRow:

LOAD MAX(Row#) AS NumberofRows

Resident SalesData;

//Store Max Row Number into a variable

LET vNumberOfRows = peek('NumberofRows',0,'TempToGetMaxRow'); //This will be how many rows we will have to loop through

DROP TABLES TempToGetMaxRow; //Clean up

SET vFir[Order Number] = Chr(Floor(Rand() * 26) + 65)&Chr(Floor(Rand() * 25) + 65)&Chr(Floor(Rand() * 24) + 65)&Chr(Floor(Rand() * 23) + 65)&Chr(Floor(Rand() * 22) + 65)&Chr(Floor(Rand() * 21) + 65);

for l=0 to $(vNumberOfRows)

  RandSalesData:

    Load *,

         if(l=0,$(vFir[Order Number]),if($(vCurrent[Order Number])=$(vPre[Order Number]),$(vPreRand[Order Number]),$(vThisRand[Order Number]))) as Rand[Order Number]

    Resident SalesData

    WHERE RecNo()= $(l)

    ;

Next l;

    Set vCurrent[Order Number] = peek('[Order Number]',i,'SalesData');

    Set vPre[Order Number] = peek('[Order Number]',(i-1),'SalesData');

//     Set vThisRand[Order Number] = Chr(Floor(Rand() * 26) + 65)&Chr(Floor(Rand() * 25) + 65)&Chr(Floor(Rand() * 24) + 65)&Chr(Floor(Rand() * 23) + 65)&Chr(Floor(Rand() * 22) + 65)&Chr(Floor(Rand() * 21) + 65);

    Set vThisRand[Order Number] = '$(vFir[Order Number])'

    Set vPreRand[Order Number] = '$(vThisRand[Order Number])';

Drop Table SalesData;