Max(Date) as Flag

jmryan — Wed, 04 May 2022 16:16:47 GMT

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?

Re: Max(Date) as Flag

vchuprina — Wed, 04 May 2022 21:08:47 GMT

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

Re: Max(Date) as Flag

jmryan — Thu, 05 May 2022 13:42:49 GMT

This seems to have worked- thank you!

topic Max(Date) as Flag in App Development

Max(Date) as Flag

Re: Max(Date) as Flag

Re: Max(Date) as Flag