Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
Master II
Master II

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
Master II
Master II

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

Taoufiq_Zarra
Master II
Master II

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