Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends ,
In my below code , i have flag for working day and non working day , but i also want one column having 7th working date for each month
LET vWeeklyHolidays = 'Sat;Sun';
TempDate:
LOAD
YearStart(Today()) + RowNo() - 1 AS TempDate
Autogenerate(365);
CalendarMaster_Temp:
LOAD
Date(TempDate) AS TempDate,
Year(TempDate) AS Year,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
Month(TempDate) As Month,
Day(TempDate) As Day,
WeekDay(TempDate) AS WeekDay,
Week(TempDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS TempDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(TempDate) AS MinDate,
Max(TempDate) AS MaxDate
RESIDENT TempDate;
LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Holiday) AS TempDate,
Description AS HolidayDescription,
1 AS IsHolidayFlag
FROM
(ooxml, embedded labels, table is Holidays);
Calendar:
LOAD
TempDate,
TempDate AS Date,
Year,
Quarter,
Month,
Day,
WeekDay,
Week,
HolidayDescription,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(TempDate)) OR IsHolidayFlag = 1, 0, 1) AS IsHolidayFlag,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(TempDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;
DROP TABLE CalendarMaster_Temp;
DROP TABLE TempDate;
Maybe something like
LOAD
TempDate,
If( LastWorkDate(Monthstart(TempDate),7) =TempDate,1,0) as IsSeventhWorkingDayInMonth
You can add a list of holidays to LastWorkDate() function, if you want and need it.
please explain little more about ,
You can add a list of holidays to LastWorkDate() function
how to do this ?
How can i add holidays , because i need 7th working day.
or you can try something like this
If(NetWorkDays(Monthstart(Date),Date,'05/01/2017','07/04/2017','12/25/2017')=7,Date) as IsSeventhWorkingDayInMonth
'05/01/2017','07/04/2017','12/25/2017' are list of holidays!!
Basically, it's explained in
You can read your external list of holidays (excel sheet) in a QV table and aggregate the records to a comma separated list of values, finally stored to a variable.
It's demonstrated in
Hi Friends ,
in my attached , file may 2017 , 7th working day should be 11th May 2017.
Can you please see what doing wrong here .
The variable should go into the LastWorkDay() function, not as argument to the If() function.
If( LastWorkDate(Monthstart(TempDate),7, $(vHolDays)) =TempDate,1,0) as IsSeventhWorkingDayInMonth |
Also, the list of holidays should enclose each holiday in single quotes:
Holidays:
Load
concat(chr(39)&Holiday &chr(39),',') as Holidays
FROM
(ooxml, embedded labels, table is Holidays);
if i want same as first working date for month then can i use like
If(FirstWorkDate(Monthstart(TempDate),0, $(vHolDays)) =TempDate,1,0) as IsFirstWorkingDayInMonth
No...
Why not just change 7 to 1?
If( LastWorkDate(Monthstart(TempDate),1, $(vHolDays)) =TempDate,1,0) |