Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Meisters,
Question for you:
Given a table of object | Start Date | End Date
Is it possible to add a new row that is a flag of whether or not the range contains a certain weekday (like Sunday, Monday, etc)?
Example (For Sundays):
obj | start | end | yesOrNo
A | 6/1/2020 | 6/5/2020 | No
B| 6/4/2020 | 6/9/ 2020 | Yes
Regards,
S
one solution :
Set DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
/*
0 for Monday
1 for Tuesday
2 for Wednesday
3 for Thursday
4 for Friday
5 for Saturday
6 for Sunday
*/
let VWeekDay=6;
Data:
load *,if(WeekStart(Date#(end,'MM/DD/YYYY'),0,$(VWeekDay)) >= Date#(start,'MM/DD/YYYY') and WeekStart(Date#(end,'MM/DD/YYYY'),0,$(VWeekDay)) <= Date#(end,'MM/DD/YYYY'),'YES','NO') as yesOrNo inline [
obj | start | end
A | 6/1/2020 | 6/5/2020
B| 6/4/2020 | 6/9/2020
](delimiter is '|');
let VWeekDay=6; if you want to flag Sundays
other :
0 for Monday
1 for Tuesday
2 for Wednesday
3 for Thursday
4 for Friday
5 for Saturday
6 for Sunday
so for example VWeekDay=6 the output :
other example for Monday so
let VWeekDay=0;
output :
.....