Solved: Add new Rows by Date - Qlik Community

AleRods88 · ‎2020-06-24

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

26

19-20

10

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!!!

Taoufiq_Zarra · ‎2020-06-24

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.

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Taoufiq_Zarra · ‎2020-06-24

@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;

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

AleRods88 · ‎2020-06-24

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.

Taoufiq_Zarra · ‎2020-06-24

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.

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

AleRods88 · ‎2020-06-24

Thank you!!