Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AleRods88
Contributor III
Contributor III

Generate Rows depending on 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

One Solution :

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") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

One Solution :

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") 😉
Taoufiq_Zarra

output:

Taoufiq_ZARRA_0-1592992775611.png

 

Regards,
Taoufiq ZARRA

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

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