Max(Date) as Flag

[jmryan]

I am trying to flag the max date for an employee ID field.

Example:

EmployeeID	EffDate	MaxDateFlag
1	02/15/2020	-
1	01/01/2019	-
1	03/01/2021	1
2	03/20/2020	1
3	01/15/2018	-
3	01/20/2019	1

Currently using the following load script:

job_history:

Qualify*;
Unqualify Date;

LOAD
[annualSalary],
[companyId],
[employeeId],
[employeeType],
[employeeStatus],
[fullTimeOrPartTime],
[isPromotion],
[isRateChange],
[isTransfer],
[jobCode],
[jobDescription],
[jobEffectiveDate] as Date,
[locationCode],
[orgLevel1Code],
[orgLevel2Code],
[orgLevel3Code],
[payGroupCode],
[reasonCode],
[salaryOrHourly],
[supervisorId],
[systemId],
[homeCompanyId],
[integrationEffectiveDate],
[jobTitle]

RESIDENT RestConnectorMasterTable;

Left Join (job_history)

LOAD
[job_history.employeeId],
Max(Date) as Date,
1 as MaxDateFlag

RESIDENT job_history

Group By [job_history.employeeId];

DROP TABLE RestConnectorMasterTable;

This is giving me a resulting:

job_history.employeeId	Date	job_history-5.MaxDateFlag
1	2015-04-17	1
1	2015-08-21	1

What am I missing here?

[vchuprina · Accepted solution]

Hi,

You should add UNQUALIFY *; before 

LOAD
[job_history.employeeId],
Max(Date) as Date,
1 as MaxDateFlag

RESIDENT job_history;

In your script, you have Unqualify Date, but job_history.employeeId is still qualified, for this reason, you join your tables only by the Date field

Regards,

VItalii

[jmryan]

This seems to have worked- thank you!