Generate missing data for each and every record

[pushwithqlik]

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 1^st 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 2^nd 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 1^st row (after generation it will have 25 records) storing it in temporary table and when loop comes to 2^nd record, concatenating these generated records with the previous 25 records, similarly for 3^rd row ,4^th 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.

[Gysbert_Wassenaar]

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

[pushwithqlik]

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..!

[Gysbert_Wassenaar]

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.