Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

Load most recent data only

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.

8 Replies
sudeepkm
Valued Contributor III

Re: Load most recent data only

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
New Contributor II

Re: Load most recent data only

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

TKendrick20
Valued Contributor

Re: Load most recent data only

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
Valued Contributor II

Re: Load most recent data only



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
New Contributor II

Re: Load most recent data only

Prem and Tim,

Neither one of these worked.

Any other thoughts?

MVP
MVP

Re: Load most recent data only

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
Valued Contributor III

Re: Re: Load most recent data only

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

Not applicable

Re: Load most recent data only

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

Community Browser