Random data generation for sales data

[rammarthi]

Hi,

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

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,

attached is the excel file for reference.

[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]

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;