Fill in Missing Rows from Date

[AleRods88]

Hello,

I have the following table with columns:

- DATE: Format DD/MM/YYYY

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
7/3/2020	2020-10	10	19-20	5	sat
14/3/2020	2020-11	11	19-20	8	sat
21/3/2020	2020-12	12	19-20	3	sat
28/3/2020	2020-13	13	19-20	7	sat
4/4/2020	2020-14	14	19-20	4	sat
11/4/2020	2020-15	15	19-20	6	sat
18/4/2020	2020-16	16	19-20	8	sat
25/4/2020	2020-17	17	19-20	25	sat
2/5/2020	2020-18	18	19-20	10	sat
3/5/2020	2020-19	19	19-20	10	sun
4/5/2020	2020-19	19	19-20	10	mon

So I need to generate a new row each day with the same data, except fields DATE and YEAR_WEEK (this field has the number of the week). And this would be my data on saturday 09/05/2020 (DD/MM/YYYY):

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
7/3/2020	2020-10	10	19-20	5	sat
14/3/2020	2020-11	11	19-20	8	sat
21/3/2020	2020-12	12	19-20	3	sat
28/3/2020	2020-13	13	19-20	7	sat
4/4/2020	2020-14	14	19-20	4	sat
11/4/2020	2020-15	15	19-20	6	sat
18/4/2020	2020-16	16	19-20	8	sat
25/4/2020	2020-17	17	19-20	25	sat
2/5/2020	2020-18	18	19-20	10	sat
3/5/2020	2020-19	19	19-20	10	sun
4/5/2020	2020-19	19	19-20	10	mon
5/5/2020	2020-19	19	19-20	10	tue
6/5/2020	2020-19	19	19-20	10	wed
7/5/2020	2020-19	19	19-20	10	thu
8/5/2020	2020-19	19	19-20	10	fri
9/5/2020	2020-19	19	19-20	10	sat

But on sunday, 10/05 (DD/MM), I would need to delete those records in blue because when the week is over, I need to keep only the record from saturday:

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
7/3/2020	2020-10	10	19-20	5	sat
14/3/2020	2020-11	11	19-20	8	sat
21/3/2020	2020-12	12	19-20	3	sat
28/3/2020	2020-13	13	19-20	7	sat
4/4/2020	2020-14	14	19-20	4	sat
11/4/2020	2020-15	15	19-20	6	sat
18/4/2020	2020-16	16	19-20	8	sat
25/4/2020	2020-17	17	19-20	25	sat
2/5/2020	2020-18	18	19-20	10	sat
9/5/2020	2020-19	19	19-20	10	sat
10/5/2020	2020-19	20	19-20	10	sun

And then repeat the process automatically.

Is there any way to do that by script?

Thank you!

[Taoufiq_Zarra · Accepted solution]

@AleRods88  A very nice practice, thank you 😁

attached you'll find the qlikview fil.

Script :

InitialData:
/*
LOAD Date(Date#(DATE,'DD/MM/YYYY')) as DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, Date(Date#(DATE,'DD/MM/YYYY'),'WWW') as DAY INLINE [
    DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, DAY
    7/3/2020, 2020-10, 10, 19-20, 5, sat
    14/3/2020, 2020-11, 11, 19-20, 8, sat
    21/3/2020, 2020-12, 12, 19-20, 3, sat
    28/3/2020, 2020-13, 13, 19-20, 7, sat
    4/4/2020, 2020-14, 14, 19-20, 4, sat
    11/4/2020, 2020-15, 15, 19-20, 6, sat
    18/4/2020, 2020-16, 16, 19-20, 8, sat
    25/4/2020, 2020-17, 17, 19-20, 25, sat
    2/5/2020, 2020-18, 18, 19-20, 10, sat
    3/5/2020, 2020-19, 19, 19-20, 10, sun
    4/5/2020, 2020-19, 19, 19-20, 10, mon
];
*/

LOAD Date(Date#(DATE,'DD/MM/YYYY')) as DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, Date(Date#(DATE,'DD/MM/YYYY'),'WWW') as DAY INLINE [
    DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, DAY
    7/3/2020, 2020-10, 10, 19-20, 5, sat
    14/3/2020, 2020-11, 11, 19-20, 8, sat
    21/3/2020, 2020-12, 12, 19-20, 3, sat
    28/3/2020, 2020-13, 13, 19-20, 7, sat
    4/4/2020, 2020-14, 14, 19-20, 4, sat
    11/4/2020, 2020-15, 15, 19-20, 6, sat
    18/4/2020, 2020-16, 16, 19-20, 8, sat
    25/4/2020, 2020-17, 17, 19-20, 25, sat
    2/5/2020, 2020-18, 18, 19-20, 10, sat
    9/5/2020, 2020-19, 19, 19-20, 10, sat
    10/5/2020, 2020-19, 20, 19-20, 10, sun
];


Temp1:
noconcatenate

load * resident InitialData where num(WeekDay(Date(Date#(DATE,'DD/MM/YYYY'))))=5;

Temp2:
noconcatenate

load Max(DATE) as MaxDate resident InitialData ;

Let VMaxdate=num(WeekDay(peek('MaxDate',0,'Temp2')));

drop table Temp2;

let concatenate=0;

if $(VMaxdate) <> 5 then

	let concatenate=1;
	
			Temp3:
			noconcatenate
			First 1 load * resident InitialData order by DATE DESC;
			let Vmin=num(floor(weekStart(peek('DATE',0,'Temp3'),0,6)));
			let Vmax=num(floor(WeekEnd(peek('DATE',0,'Temp3'),0,6)));
			let vCAMPAIGN=peek('CAMPAIGN',0,'Temp3');
			let vVALUE=peek('VALUE',0,'Temp3');
			
			drop table Temp3;
			
				Table:
				noconcatenate
							
							Load
			
			               Date($(Vmin) + IterNo() - 1,'DD/MM/YYYY') as DATE,
			               week($(Vmin) + IterNo() - 1) As WEEK,
			               Year($(Vmin) + IterNo() - 1)&'-'&week($(Vmin) + IterNo() - 1) as YEAR_WEEK,
			               Date(($(Vmin) + IterNo() - 1),'WWW') as DAY,
						   '$(vCAMPAIGN)' as CAMPAIGN,
						   '$(vVALUE)' as VALUE 
			               AutoGenerate 1 While $(Vmin) + IterNo() -1 <= $(Vmax);
	
	       
end if

drop table InitialData;



if $(concatenate) = 1 then 
			output:
			noconcatenate
			load * resident Temp1;
			concatenate load * resident Table;
			drop table Temp1,Table;
else
			output:
			noconcatenate
			load * resident Temp1;
			drop table Temp1;
end if

the result obtained for the sample 1 :

DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, DAY
7/3/2020, 2020-10, 10, 19-20, 5, sat
14/3/2020, 2020-11, 11, 19-20, 8, sat
21/3/2020, 2020-12, 12, 19-20, 3, sat
28/3/2020, 2020-13, 13, 19-20, 7, sat
4/4/2020, 2020-14, 14, 19-20, 4, sat
11/4/2020, 2020-15, 15, 19-20, 6, sat
18/4/2020, 2020-16, 16, 19-20, 8, sat
25/4/2020, 2020-17, 17, 19-20, 25, sat
2/5/2020, 2020-18, 18, 19-20, 10, sat
3/5/2020, 2020-19, 19, 19-20, 10, sun
4/5/2020, 2020-19, 19, 19-20, 10, mon

and for this sample :

7/3/2020, 2020-10, 10, 19-20, 5, sat
14/3/2020, 2020-11, 11, 19-20, 8, sat
21/3/2020, 2020-12, 12, 19-20, 3, sat
28/3/2020, 2020-13, 13, 19-20, 7, sat
4/4/2020, 2020-14, 14, 19-20, 4, sat
11/4/2020, 2020-15, 15, 19-20, 6, sat
18/4/2020, 2020-16, 16, 19-20, 8, sat
25/4/2020, 2020-17, 17, 19-20, 25, sat
2/5/2020, 2020-18, 18, 19-20, 10, sat
9/5/2020, 2020-19, 19, 19-20, 10, sat
10/5/2020, 2020-19, 20, 19-20, 10, sun