Max Start Date

[tmumaw]

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

[sunny_talwar]

May be just this:

Date(Max([Start Date]))

[tmumaw]

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
(qvd)
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;

[sunny_talwar · Accepted solution]

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
(qvd)
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];

[rahulpawarb]

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

[sunny_talwar]

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

[tmumaw]

Thanks Sunny T.  Worked perfect.

[rahulpawarb]

Hello Sunny,

My sincere apologies. I misunderstood the requirement. You are correct.

Regards!

Rahul

[sunny_talwar]

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

1. Data:
2. LOAD * INLINE [
3. EmployeeNo, StartDate
4. 0111, 40250
5. 0111, 40251
6. 0111, 40260
7. 0111, 40150
8. ];
9. INNER JOIN
10. LOAD EmployeeNo,
11.     MAX(StartDate) AS StartDate
12. Resident Data
13. GROUP BY EmployeeNo;

This is all you needed