Load most recent data only

[jenmclean]

I am working on Employee Time reporting and I have a table in the Database that holds the employee company, location and area. In scripting a QVD for this information, I only want to pull in the location tied to that employee, which is "depth = 2", BUT, the employee record can hold multiple changes and I want only the most recent information, Below is an example employee who has switched locations within the company; org_level_id is the location.

employee_id      depth      effective_date                org_level_id      stop_date

109                     1             2006-12-31 00:00:00      1                         2010-10-30 00:00:00

109 1 2010-10-31 00:00:00 1 2079-06-06 00:00:00

109                     2              2006-12-31 00:00:00      16                     2010-10-30 00:00:00  OLD

109                     2              2010-10-31 00:00:00      18                     2079-06-06 00:00:00   LATEST CHANGE

109 3 2006-12-31 00:00:00 76 2010-10-30 00:00:00

109 3 2010-10-31 00:00:00 84 2079-06-06 00:00:00

My current script is a follows:

SQL SELECT *
FROM TimeStar.dbo.employee_org_level
WHERE [depth] = '2';
Store * FROM Employee_OrgLevel into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TimeStar\Employee_OrgLevel.qvd (qvd);

How do I script this to pull in ONLY the most recent information which is shown as green in my sample data above. The effective_date column is based on when that record was last modified.

[sudeepkm]

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.

[jenmclean]

Not sure how to script this, can you provide an example?

[TKendrick20]

Try something like this?

SQL SELECT DISTINCT employee_ID,

depth,

Max(effective_date),

org_level_id,

stop_date
FROM TimeStar.dbo.employee_org_level
WHERE [depth] = '2'

GROUP BY employee_ID, depth, org_level_id, stop_date

Store * FROM Employee_OrgLevel into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TimeStar\Employee_OrgLevel.qvd (qvd);

[preminqlik]

FINALTABLE:

Load employee_id ,   

depth   , 

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 *

FROM TimeStar.dbo.employee_org_level
WHERE [depth] = '2';
Store * FROM Employee_OrgLevel into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TimeStar\Employee_OrgLevel.qvd (qvd);

[jenmclean]

Prem and Tim,

Neither one of these worked.

Any other thoughts?

[maxgro]

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

tmp:

sql select .......

final:

NoConcatenate

load * Resident tmp

where peek(employee_id)<>employee_id

order by employee_id, effective_date desc

;

[sudeepkm]

Please take a look at the example attached. If you are not able to open the qvw then please let me know.

[#9]

Hi Jennie, You can do this in SQL Query itself & that would be better performance rather than Using Resident.

SELECT

     A.*

FROM TimeStar.dbo.employee_org_level A

INNER JOIN

     (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';