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:
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);
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);
