Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create every other Friday off in order to calculate a payday and overtimes.
Below is my pseudo file
Origin:
load * Inline [ monthYear
Feb-21
Mar-21
Apr-21
]
;
temp:
LOAD
IdDate,
Date(IdDate) as Date,
Month(IdDate) as Month,
Num(Month(IdDate)) as NumMonth,
Year(IdDate) as Year
;
LOAD
MinDate + IterNo()-1 as IdDate
While MinDate + IterNo()-1 <= MaxDate
;
LOAD
Floor(Min(Date#(FieldValue('monthYear', RecNo()), 'MMM-YY'))) as MinDate,
Floor(Max(Date#(FieldValue('monthYear', RecNo()), 'MMM-YY'))) as MaxDate
Resident Origin;
Then I created every other friday set with the code as below
Date(div(weekstart(Date)+5,14)) as BiWeek Resident temp;
Then it created data from 1908..???
1) How can I make it start from 2/5/2021?
2) can I create list of [2/5/2021, 2/18/2021, 3/5/2021, 3/19/2021...] and so on, instead of the same date(8/24/1908) is repeated?
Based on the dates displayed, div(weekstart(Date)+5,14) = 3159 which is 8/24/1908. If 2/5/2021 is a paydate, I would create a PaydayFlag
temp:
LOAD
ldDate,
Date(ldDate) as Date,
Month(ldDate) as Month,
Num(Month(ldDate)) as NumMonth,
Year(ldDate) as Year,
If ((Mod(ldate - Date#('2/5/2021'),14) = 0,1,0) as PaydayFlag //1=Pay Friday
;
Based on the dates displayed, div(weekstart(Date)+5,14) = 3159 which is 8/24/1908. If 2/5/2021 is a paydate, I would create a PaydayFlag
temp:
LOAD
ldDate,
Date(ldDate) as Date,
Month(ldDate) as Month,
Num(Month(ldDate)) as NumMonth,
Year(ldDate) as Year,
If ((Mod(ldate - Date#('2/5/2021'),14) = 0,1,0) as PaydayFlag //1=Pay Friday
;
Thank you
If ((Mod(IdDate - Date#('2/5/2021'),14) = 0), 1, 0) as PaydayFlag creates 1 and 0 and good to categorizes working-Friday and non-working Friday efficiently.
But is there any way of creating list of [2/5/2021, 2/18/2021, 3/5/2021, 3/19/2021...] ?
Just select Date from the calendar where PaydayFlag = 1.