3 Replies Latest reply: Sep 21, 2016 12:09 PM by Gysbert Wassenaar RSS

    Generate missing data for each and every record

    Pushpa latha

      The requirement is to generate missing data, I have a table consists of 2 million plus records with 40 fields and sample data as follows:

      TKey

      DS

      Year

      Sales

      SalesVolume

      PJA14

      P21

      04/01/2014

      0

      0.8

      PJA14

      P21

      06/01/2014

      0

      5.3

      PJA14

      P21

      04/01/2014

      0

      0.008

      PJA14

      P21

      04/01/2015

      0

      2.3

      PJA14

      P21

      04/01/2016

      0

      6.7

       

      My datasource here is QVD file. The min date is 04/01/2014 and max date is 04/01/2016 so we have to generate dates between min & max dates and copy the previous record to the next generated records and should differentiate the actual and copied data .i.e., look at the example below the datasource P21 has 02/01/2016 so we generated dates and copied the data to generated records.

       

      Datasource

      category

      MonthStart

      Actual or copied

      P21

      PL

      02/01/2016

      Actual

      P21_c (Differentiated copied date with _c

      PL

      03/01/2016

      03/01/2016 data is generated so carry forwarded previous month data

      P21_c (Differentiated copied date with _c

      PL

      04/01/2016

      04/01/2016 data is generated so carry forwarded previous month data

       

      I have written code to loop total number of records in the table i.e., 2 million plus times , how my code working here is picking the 1st record and the date for this record is 04/01/2014 so I am generating month wise dates say 04/01/2014, 05/01/2014, 06/01/2014, 07/01/2014……04/01/2015…. and so on 04/01/2016(Generating 24 records) and then copying the data to these generated months as above example. Similarly for the 2nd record based on the date, generate dates and then copy previous data. Here after processing one after the other record I am doing the concatenation i.e, after processing 1st row (after generation it will have 25 records) storing it in temporary table and when loop comes to 2nd record, concatenating these generated records with the previous 25 records, similarly for 3rd row ,4th row …..and so on 2 millionth plus record.

       

      Therefore, loop will run 2 million plus times and when I reload the QVW, for processing 100 records it took 10 minutes, based on this we can assume the time for 2 million records.

      What is the other alternate way to achieve the requirement in terms of performance so that it should reduce the time to complete reload.