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!

[DesmoArka · Accepted solution]

hi,

maybe this helps you:

actual:
load *
from HISTORY.QVD(QVD)
where DAY=IF(WeekDay(Today())='sun','sat',DAY);
Concatenate
LOAD DATE(Today()) AS DATE,
Week(TODAY()) AS WEEK,
'19-20' AS CAMPAIGN,
Peek(VALUE) AS VALUE,
WeekDay(Today()) AS DAY,
Year(today()) & '-' & Week(today()) as YEAR_WEEK
AutoGenerate(1);

DROP Table history;

STORE * from actual into HISTORY.QVD(QVD);