2 Replies Latest reply: Nov 18, 2016 10:26 AM by Ram Marthi RSS

    Random data generation for sales data

    Ram Marthi

      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.

        • Re: Random data generation for sales data
          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.?

          • Re: Random data generation for sales data
            Ram Marthi

            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;