Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You're probably looking for this blog post: How to populate a sparsely populated field. If not then perhaps this document helps: Generating Missing Data In QlikView
Thanks for your reply..!!
My code is working for the given requirement but the only thing here is performance.To Process 100 records it took 10 minutes, based on this we can assume time for 2 million records. Is there any alternate way to do this? or what is the way to acheive in terms of performance. Thanks in advance..!
I've already pointed out an alternative way above. If you can't be bothered to try it then you're beyond any help I can offer.