How to determine which weekday of the month

[LORENZ_GRN]

Hi everybody, I'm asking you if there is a way in the loading script to programmatically  determine which number of weekday is in a given month. For example if I have a date like  (23/11/2021) I would like to determine that it is the 4th tuesday of November.  In addition, I would like to go to the 4th tuesday of December, which is the 28.

Thank you very much indeed

PS I attached a screenshot

[tm_burgers · Accepted solution]

Create a keyfield for the Weekday/Month/Year like : 

Year(DateTable)&'-'&num(Month(DateTable))&'-'&WeekDay(DateTable) as YearMonthWeekday

Then load up these values in a seperate query and order them by that new field, you can check if the row above = current row and then peek occurence + 1. 

See script below:

DateTable:
LOAD
DateTable as MasterDate,
Year(DateTable) as Year,
num(Month(DateTable)) as MonthNum,
WeekDay(DateTable) as WeekDay,
Year(DateTable)&'-'&num(Month(DateTable))&'-'&WeekDay(DateTable) as YearMonthWeekday
FROM [lib://Calendar.xlsx]
(ooxml, embedded labels, table is Sheet1);


left join (DateTable)
LOAD
MasterDate,
YearMonthWeekday,
if(Previous(YearMonthWeekday)=YearMonthWeekday,Peek(Occurrence)+1,1) as Occurrence
Resident DateTable
Order by YearMonthWeekday;

Your second part should be fairly easy = if 4 then currentmonth + 1 & occurrence = 4

[LORENZ_GRN · Accepted solution]

Hi @tm_burgers  thank you very much.

I found an alternative solution, the field "Calendar.WeekDayCount" is your "Occurence", here is my script:

tb_WeekDayCount:
load num(floor(DATE)) as Date,
if(DAY<=7,1,rangesum(peek('Calendar.WeekDayCount',-7),1)) as Calendar.WeekDayCount,
;
LOAD *,
day(DATE) as DAY,
num(weekday(DATE))+1 as WeekDayNum,
;
LOAD DayName(43831,RecNo()-1) as DATE
AutoGenerate 2000;