Fill in Missing Rows from Date

AleRods88 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Fill in Missing Rows from Date

Taoufiq_Zarra — Tue, 16 Jun 2020 18:19:19 GMT

@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

topic Re: Fill in Missing Rows from Date in QlikView

Fill in Missing Rows from Date

Re: Fill in Missing Rows from Date