Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning everyone,
Simple question for you all. I need to get the max start date from this sample group of employees. What is the easy way to do it? I have already converted the start date to a numeric field. I only want to see the numeric start date equal to 42734.
Thanks
In the script, may be try this:
Temp:
LOAD [End Date],
[Start Date],
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
LinkKey,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;
Right Join (Temp)
LOAD [Employee Number],
Max([Start Date]) as [Start Date]
Resident Temp
Group By [Employee Number];
May be just this:
Date(Max([Start Date]))
Where would you use this? To only get one record don't you have to do a group by?
Temp:
LOAD [End Date],
[Start Date],
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
LinkKey,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;
In the script, may be try this:
Temp:
LOAD [End Date],
[Start Date],
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
LinkKey,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;
Right Join (Temp)
LOAD [Employee Number],
Max([Start Date]) as [Start Date]
Resident Temp
Group By [Employee Number];
Hello Thom,
Trust you are doing good!
I agree with Sunny. However, you can refer below script to solve your concern.
Data:
LOAD * INLINE [
EmployeeNo, StartDate
0111, 40250
0111, 40251
0111, 40260
0111, 40150
];
INNER JOIN
LOAD EmployeeNo,
MAX(StartDate) AS NumStartDate
Resident Data
GROUP BY EmployeeNo;
Hope this will be helpful.
Regards!
Rahul
This is not going to work because you renamed startdate to numstartdate. The inner join will occur only on EmployeeNo keeping all the 4 rows intact
Thanks Sunny T. Worked perfect.
Hello Sunny,
My sincere apologies. I misunderstood the requirement. You are correct.
Regards!
Rahul
You don't have apologize brother. I was just pointing out that in order for your solution to work, you would just need to keep the name of the field you are joining on the same as it is in the above table
This is all you needed