topic Add new Rows by Date in QlikView

Add new Rows by Date

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

Hi,

I have the following table with these columns (where DATE format is MM/DD/YYYY):

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
03/07/2020	2020-10	10	19-20	5	sat
03/14/2020	2020-11	11	19-20	8	sat
03/21/2020	2020-12	12	19-20	3	sat
03/28/2020	2020-13	13	19-20	7	sat
04/04/2020	2020-14	14	19-20	4	sat
04/11/2020	2020-15	15	19-20	6	sat
04/18/2020	2020-16	16	19-20	8	sat
04/25/2020	2020-17	17	19-20	25	sat
05/02/2020	2020-18	18	19-20	10	sat
05/09/2020	2020-19	19	19-20	10	sat
05/16/2020	2020-20	20	19-20	10	sat
05/23/2020	2020-21	21	19-20	10	sat
05/30/2020	2020-22	22	19-20	10	sat
06/06/2020	2020-23	23	19-20	10	sat
06/13/2020	2020-24	24	19-20	10	sat
06/20/2020	2020-25	25	19-20	10	sat
06/23/2020	2020-26	26	19-20	10	tue

The rows in black are "permanent" (or fixed) and I should always show them. The rows in blue are temporary and vary from day to day (please note that the colors are only for the explanation, but I don't need the colors for the development). So I need to replace the row in blue each day, by using the same data from the previous date, except fields DATE (replacing it for the current date) and YEAR_WEEK (this field has the number of the week).

This means, tomorrow wednesday 06/24/2020 I need to replace the last row in blue (removing tuesday) for this one:

06/24/2020

2020-26

19-20

wed

The same for thursday 06/25, friday 06/26 and saturday 06/27. So I would show the data in black rows (permanent) and the blue row (dynamic) for the current day. When I run the script on sunday, I should do 2 things:

1 - Keep the row from saturday like the other rows I have in black in the table (I won't replace this row anymore). So the row for saturday should go from "blue" to "black" (now keeping and always showing this row).

2 - Generate a new row for sunday 06/28, where this row would be replaced by monday 06/29, then tuesday, wednesday, thursday, friday, saturday. And then again, repeat the process automatically: keep the row from saturday and generate a new row on sunday.

Here are a few examples:

Data on thursday 06/25:

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
03/07/2020	2020-10	10	19-20	5	sat
03/14/2020	2020-11	11	19-20	8	sat
03/21/2020	2020-12	12	19-20	3	sat
03/28/2020	2020-13	13	19-20	7	sat
04/04/2020	2020-14	14	19-20	4	sat
04/11/2020	2020-15	15	19-20	6	sat
04/18/2020	2020-16	16	19-20	8	sat
04/25/2020	2020-17	17	19-20	25	sat
05/02/2020	2020-18	18	19-20	10	sat
05/09/2020	2020-19	19	19-20	10	sat
05/16/2020	2020-20	20	19-20	10	sat
05/23/2020	2020-21	21	19-20	10	sat
05/30/2020	2020-22	22	19-20	10	sat
06/06/2020	2020-23	23	19-20	10	sat
06/13/2020	2020-24	24	19-20	10	sat
06/20/2020	2020-25	25	19-20	10	sat
06/25/2020	2020-26	26	19-20	10	thu

Data on saturday 06/27:

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
03/07/2020	2020-10	10	19-20	5	sat
03/14/2020	2020-11	11	19-20	8	sat
03/21/2020	2020-12	12	19-20	3	sat
03/28/2020	2020-13	13	19-20	7	sat
04/04/2020	2020-14	14	19-20	4	sat
04/11/2020	2020-15	15	19-20	6	sat
04/18/2020	2020-16	16	19-20	8	sat
04/25/2020	2020-17	17	19-20	25	sat
05/02/2020	2020-18	18	19-20	10	sat
05/09/2020	2020-19	19	19-20	10	sat
05/16/2020	2020-20	20	19-20	10	sat
05/23/2020	2020-21	21	19-20	10	sat
05/30/2020	2020-22	22	19-20	10	sat
06/06/2020	2020-23	23	19-20	10	sat
06/13/2020	2020-24	24	19-20	10	sat
06/20/2020	2020-25	25	19-20	10	sat
06/27/2020	2020-26	26	19-20	10	sat

Data on sunday 06/28:

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
03/07/2020	2020-10	10	19-20	5	sat
03/14/2020	2020-11	11	19-20	8	sat
03/21/2020	2020-12	12	19-20	3	sat
03/28/2020	2020-13	13	19-20	7	sat
04/04/2020	2020-14	14	19-20	4	sat
04/11/2020	2020-15	15	19-20	6	sat
04/18/2020	2020-16	16	19-20	8	sat
04/25/2020	2020-17	17	19-20	25	sat
05/02/2020	2020-18	18	19-20	10	sat
05/09/2020	2020-19	19	19-20	10	sat
05/16/2020	2020-20	20	19-20	10	sat
05/23/2020	2020-21	21	19-20	10	sat
05/30/2020	2020-22	22	19-20	10	sat
06/06/2020	2020-23	23	19-20	10	sat
06/13/2020	2020-24	24	19-20	10	sat
06/20/2020	2020-25	25	19-20	10	sat
06/27/2020	2020-26	26	19-20	10	sat
06/28/2020	2020-27	27	19-20	10	sun

Data on monday 06/29:

DATE	YEAR_WEEK	WEEK	CAMPAIGN	VALUE	DAY
03/07/2020	2020-10	10	19-20	5	sat
03/14/2020	2020-11	11	19-20	8	sat
03/21/2020	2020-12	12	19-20	3	sat
03/28/2020	2020-13	13	19-20	7	sat
04/04/2020	2020-14	14	19-20	4	sat
04/11/2020	2020-15	15	19-20	6	sat
04/18/2020	2020-16	16	19-20	8	sat
04/25/2020	2020-17	17	19-20	25	sat
05/02/2020	2020-18	18	19-20	10	sat
05/09/2020	2020-19	19	19-20	10	sat
05/16/2020	2020-20	20	19-20	10	sat
05/23/2020	2020-21	21	19-20	10	sat
05/30/2020	2020-22	22	19-20	10	sat
06/06/2020	2020-23	23	19-20	10	sat
06/13/2020	2020-24	24	19-20	10	sat
06/20/2020	2020-25	25	19-20	10	sat
06/27/2020	2020-26	26	19-20	10	sat
06/29/2020	2020-27	27	19-20	10	mon

Is there any way to do that by script? (I would run the script every day).

Thank you!!!

Re: Add new Rows by Date

Taoufiq_Zarra — Wed, 24 Jun 2020 13:38:02 GMT

@AleRods88

Did you check my solution :

https://community.qlik.com/t5/New-to-QlikView/Generate-Rows-depending-on-Date/m-p/1721493#M390443

Data: LOAD * INLINE [ DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, DAY 03/07/2020, 2020-10, 10, 19-20, 5, sat 03/14/2020, 2020-11, 11, 19-20, 8, sat 03/21/2020, 2020-12, 12, 19-20, 3, sat 03/28/2020, 2020-13, 13, 19-20, 7, sat 04/04/2020, 2020-14, 14, 19-20, 4, sat 04/11/2020, 2020-15, 15, 19-20, 6, sat 04/18/2020, 2020-16, 16, 19-20, 8, sat 04/25/2020, 2020-17, 17, 19-20, 25, sat 05/02/2020, 2020-18, 18, 19-20, 10, sat 05/09/2020, 2020-19, 19, 19-20, 10, sat 05/16/2020, 2020-20, 20, 19-20, 10, sat 05/23/2020, 2020-21, 21, 19-20, 10, sat 05/30/2020, 2020-22, 22, 19-20, 10, sat 06/06/2020, 2020-23, 23, 19-20, 10, sat 06/13/2020, 2020-24, 24, 19-20, 10, sat 06/20/2020, 2020-25, 25, 19-20, 10, sat 06/23/2020, 2020-26, 26, 19-20, 10, tue ]; Tmp: noconcatenate First 1 load DATE as vDATE, YEAR_WEEK as vYEAR_WEEK, WEEK as vWEEK, CAMPAIGN as vCAMPAIGN, VALUE as vVALUE, DAY as vDAY resident Data order by DATE DESC; let IDtmp=num(WeekDay(Date#(peek('vDATE',0,'Tmp'),'MM/DD/YYYY'))); let Datetmp=peek('vDATE',0,'Tmp'); output: noconcatenate load * resident Data where DATE<>'$(Datetmp)'; drop table Data; if $(IDtmp)<>5 then load Date(today()) as DATE, Year(today())&'-'&week(today()) as YEAR_WEEK, week(today()) as WEEK,vCAMPAIGN as CAMPAIGN,vVALUE as VALUE, WeekDay(Date(today())) as DAY resident Tmp; else load vDATE as DATE, vYEAR_WEEK as YEAR_WEEK, vWEEK as WEEK, vCAMPAIGN as CAMPAIGN, vVALUE as VALUE, vDAY as DAY resident Tmp; endif drop table Tmp;

Re: Add new Rows by Date

AleRods88 — Wed, 24 Jun 2020 14:08:54 GMT

Hello! Yes but I have some questions about it:

1 - What do you mean by this: if $(IDtmp)<>5 then? Isn't 5 = Friday?

2 - Where do you concatenate the new data to the original data? Because I think the code only replaces the last day with today, but when it's Sunday, it should store the record from Saturday as one more row, and keep replacing Sunday-Monday-Tuesday,etc.

3- How do you store the data, in order to use the last output, as an input the next day?

Thanks.

Re: Add new Rows by Date

Taoufiq_Zarra — Wed, 24 Jun 2020 15:18:45 GMT

Hi @AleRods88

1 - What do you mean by this: if $(IDtmp)<>5 then? Isn't 5 = Friday?

1-> default value of weekday :

0 for Monday
1 for Tuesday
2 for Wednesday
3 for Thursday
4 for Friday
5 for Saturday
6 for Sunday

5->Saturday

2-Where do you concatenate the new data to the original data? Because I think the code only replaces the last day with today, but when it's Sunday, it should store the record from Saturday as one more row, and keep replacing Sunday-Monday-Tuesday,etc.

normally for sunday I added two lines to store saturday and add sunday

3- How do you store the data, in order to use the last output, as an input the next day?

I added a load from Qvd

New version of Script :

Data: LOAD DATE, YEAR_WEEK, WEEK, CAMPAIGN, VALUE, DAY FROM .\output.qvd (qvd); Tmp: noconcatenate First 1 load DATE as vDATE, YEAR_WEEK as vYEAR_WEEK, WEEK as vWEEK, CAMPAIGN as vCAMPAIGN, VALUE as vVALUE, DAY as vDAY resident Data order by DATE DESC; let IDtmp=num(WeekDay(Date#(peek('vDATE',0,'Tmp'),'MM/DD/YYYY'))); let Datetmp=peek('vDATE',0,'Tmp'); let vCAMPAIGNtmp=peek('vCAMPAIGN',0,'Tmp'); let vVALUEtmp=peek('vVALUE',0,'Tmp'); output: noconcatenate load * resident Data where DATE<>'$(Datetmp)'; drop table Data; if $(IDtmp)<>5 then load Date(today()) as DATE, Year(today())&'-'&week(today()) as YEAR_WEEK, week(today()) as WEEK,vCAMPAIGN as CAMPAIGN,vVALUE as VALUE, WeekDay(Date(today())) as DAY resident Tmp; else load vDATE as DATE, vYEAR_WEEK as YEAR_WEEK, vWEEK as WEEK, vCAMPAIGN as CAMPAIGN, vVALUE as VALUE, vDAY as DAY resident Tmp; load Date(today()) as DATE, Year(today())&'-'&week(today()) as YEAR_WEEK, week(today()) as WEEK,'$(vCAMPAIGNtmp)' as CAMPAIGN,'$(vVALUEtmp)' as VALUE, WeekDay(Date(today())) as DAY AutoGenerate 1; endif drop table Tmp; Store output into ./output.qvd (qvd);

I haven't checked for Sunday I can't change my system date but it will work I hope very well.

Re: Add new Rows by Date

AleRods88 — Wed, 24 Jun 2020 15:23:23 GMT

Thank you!!