topic Load most recent data only in QlikView

Load most recent data only

jenmclean — Wed, 16 Jul 2014 15:10:36 GMT

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.

Re: Load most recent data only

sudeepkm — Wed, 16 Jul 2014 15:21:03 GMT

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.

Re: Load most recent data only

jenmclean — Wed, 16 Jul 2014 15:49:17 GMT

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

Re: Load most recent data only

TKendrick20 — Wed, 16 Jul 2014 16:02:58 GMT

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);

Re: Load most recent data only

preminqlik — Wed, 16 Jul 2014 16:05:55 GMT

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);

Re: Load most recent data only

jenmclean — Wed, 16 Jul 2014 16:27:16 GMT

Prem and Tim,

Neither one of these worked.

Any other thoughts?

Re: Load most recent data only

maxgro — Wed, 16 Jul 2014 16:31:45 GMT

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

;

Re: Re: Load most recent data only

sudeepkm — Thu, 17 Jul 2014 08:43:40 GMT

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

Re: Load most recent data only

Thu, 17 Jul 2014 14:19:32 GMT

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