Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have been struggling with this for weeks and finally figured I should come here for help.
I have been trying to create a flag field that will continue to return as "yes" for 4 months after the first instance for an employee, which I have done a million times before but I cannot seem to get this one to work properly.
This is what it should look like:
EID | Start Date | Flag | Probation |
215 | 2023-03-31 15:00:25 | ||
215 | 2023-04-19 18:52:30 | ||
215 | 2023-05-06 15:00:27 | 1 | Probation |
215 | 2023-07-04 15:00:32 | 1 | |
215 | 2023-07-24 15:00:32 | 1 | |
215 | 2023-09-03 15:00:32 |
Either I return nothing in any of the flag rows or a "1" in just the row with "Probation."
Are you able to help me figure out how to do this in an if statement?
could you share some data
Where do you want yes and where do you want 1 ?
load
EID,
timestamp(min("Start Date"),'YYYY-MM-DD hh:mm:ss') as mindate
resident tab group by EID;
if(EID=Previous(EID) and "Start Date"-mindate<120,'yes',1)
try this
if(EID = Previous(EID) and Date(Start Date) >= Date(AddMonths(Previous(Start Date), 4)), 1)
The 1s are supposed to start on the instance of "Probation" in the Probation field and then continue for the 4 months after. This if statement wouldn't account for that.