Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for 
Search instead for 
Did you mean: 
AleRods88
Contributor III
Contributor III

Add new Rows by Date

Hi,

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

DATEYEAR_WEEKWEEKCAMPAIGNVALUEDAY
03/07/20202020-101019-205sat
03/14/20202020-111119-208sat
03/21/20202020-121219-203sat
03/28/20202020-131319-207sat
04/04/20202020-141419-204sat
04/11/20202020-151519-206sat
04/18/20202020-161619-208sat
04/25/20202020-171719-2025sat
05/02/20202020-181819-2010sat
05/09/20202020-191919-2010sat
05/16/20202020-202019-2010sat
05/23/20202020-212119-2010sat
05/30/20202020-222219-2010sat
06/06/20202020-232319-2010sat
06/13/20202020-242419-2010sat
06/20/20202020-252519-2010sat
06/23/20202020-262619-2010tue

 

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/20202020-262619-2010wed

 

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:
DATEYEAR_WEEKWEEKCAMPAIGNVALUEDAY
03/07/20202020-101019-205sat
03/14/20202020-111119-208sat
03/21/20202020-121219-203sat
03/28/20202020-131319-207sat
04/04/20202020-141419-204sat
04/11/20202020-151519-206sat
04/18/20202020-161619-208sat
04/25/20202020-171719-2025sat
05/02/20202020-181819-2010sat
05/09/20202020-191919-2010sat
05/16/20202020-202019-2010sat
05/23/20202020-212119-2010sat
05/30/20202020-222219-2010sat
06/06/20202020-232319-2010sat
06/13/20202020-242419-2010sat
06/20/20202020-252519-2010sat
06/25/20202020-262619-2010thu

 

  • Data on saturday 06/27:
DATEYEAR_WEEKWEEKCAMPAIGNVALUEDAY
03/07/20202020-101019-205sat
03/14/20202020-111119-208sat
03/21/20202020-121219-203sat
03/28/20202020-131319-207sat
04/04/20202020-141419-204sat
04/11/20202020-151519-206sat
04/18/20202020-161619-208sat
04/25/20202020-171719-2025sat
05/02/20202020-181819-2010sat
05/09/20202020-191919-2010sat
05/16/20202020-202019-2010sat
05/23/20202020-212119-2010sat
05/30/20202020-222219-2010sat
06/06/20202020-232319-2010sat
06/13/20202020-242419-2010sat
06/20/20202020-252519-2010sat
06/27/20202020-262619-2010sat

 

  • Data on sunday 06/28:
DATEYEAR_WEEKWEEKCAMPAIGNVALUEDAY
03/07/20202020-101019-205sat
03/14/20202020-111119-208sat
03/21/20202020-121219-203sat
03/28/20202020-131319-207sat
04/04/20202020-141419-204sat
04/11/20202020-151519-206sat
04/18/20202020-161619-208sat
04/25/20202020-171719-2025sat
05/02/20202020-181819-2010sat
05/09/20202020-191919-2010sat
05/16/20202020-202019-2010sat
05/23/20202020-212119-2010sat
05/30/20202020-222219-2010sat
06/06/20202020-232319-2010sat
06/13/20202020-242419-2010sat
06/20/20202020-252519-2010sat
06/27/20202020-262619-2010sat
06/28/20202020-272719-2010sun

 

  • Data on monday 06/29:
DATEYEAR_WEEKWEEKCAMPAIGNVALUEDAY
03/07/20202020-101019-205sat
03/14/20202020-111119-208sat
03/21/20202020-121219-203sat
03/28/20202020-131319-207sat
04/04/20202020-141419-204sat
04/11/20202020-151519-206sat
04/18/20202020-161619-208sat
04/25/20202020-171719-2025sat
05/02/20202020-181819-2010sat
05/09/20202020-191919-2010sat
05/16/20202020-202019-2010sat
05/23/20202020-212119-2010sat
05/30/20202020-222219-2010sat
06/06/20202020-232319-2010sat
06/13/20202020-242419-2010sat
06/20/20202020-252519-2010sat
06/27/20202020-262619-2010sat
06/29/20202020-272719-2010mon

 

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

Thank you!!!

1 Solution

Accepted Solutions
Taoufiq_Zarra

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

4 Replies
Taoufiq_Zarra

@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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

Thank you!!