I think you can create an aggregated table by loading the max(effective_date ) and employeeid group by employee_id.
Then load the employee table and left join to this aggregated table which has employeeid and the latest effective date.
your final table will have employee id, most recent effective date and all other columns.
Load employee_id ,
FirstSortedValue(effective_date,-stop_date) as effective_date,
FirstSortedValue(stop_date,-stop_date) as stop_date,
FirstSortedValue(Org_Level_Id,-stop_date) as Org_Level_Id,
group by employee_id , depth ;
SQL SELECT *
WHERE [depth] = '2';
Store * FROM Employee_OrgLevel into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TimeStar\Employee_OrgLevel.qvd (qvd);
Try something like this?
SQL SELECT DISTINCT employee_ID,
WHERE [depth] = '2'
GROUP BY employee_ID, depth, org_level_id, stop_date
if you want last effective date by employee, you can do a resident load order by employee and effective date desc
use peek to filter only the first record by employee
sql select .......
load * Resident tmp
order by employee_id, effective_date desc
Hi Jennie, You can do this in SQL Query itself & that would be better performance rather than Using Resident.
FROM TimeStar.dbo.employee_org_level A
(SELECT EMPLOYEE_ID , MAX(EFFECTIVE_DATE) AS LATEST_EFFECTIVE_DATE FROM TimeStar.dbo.employee_org_level
WHERE depth = 2 Group BY EMPLOYEE_ID) B
ON A.EMPLOYEE_ID = B.EMPLOYE_ID AND A.EFFECTIVE_DATE = B.LATEST_EFFECTIVE_DATE
WHERE A.[depth] = '2';