Keep only latest record

[safik003]

I have raw data like below...

Employee	Dept	YearMonth
A	Banking	201812
A	Finance	201805
A	Insurance	201808
B	Banking	201803
B	Finance	201805

But I want to keep the latest data only i.e latest record per employee per Dept per YearMonth like below

Employee	Dept	YearMonth
A	Insurance	201808
B	Finance	201805

I want to handle this scenario in QlikView script only as there are many transformation. Here is the sample file with above data. Please suggest.

Test:
Load * INLINE
[Employee, Dept, YearMonth
A , Finance, 201805
A , Insurance, 201808
A , Banking, 201812
B , Finance, 201805
B , Banking, 201803];

@sunny_talwar 

[Vegar · Accepted solution]

Take a look at the script below, but as you'll notice I where not sure if you wanted the latest transactions per user or latest YearPeriod per user.:

Test:
Load rowno() as ID, * INLINE
[Employee, Dept, YearMonth
A        , Finance, 201805
A        , Insurance, 201808
A        , Banking, 201812
B		,  Finance, 201805
B		,  Banking, 201803];

INNER JOIN 
LOAD 
	Employee,
	Max(YearMonth) as YearMonth 	//Use if it is the lates YearMonth per employee
	//Max(ID) as ID  				//Use if it is the latest transaction that ist o be considered per empl.
Resident 
	Test
Group By 
	Employee;

.

[AVINASH22]

Use aggr function taking expression as Yearmonth with dimension employee

[deshikas]

See attached

[Brett_Bleess]

Safikul, did any of the posts help you get a working solution?  If so, please be sure to use the Accept as Solution button on the post(s) that did help you to give the poster credit and let others know which one(s) worked.  If you did something else, consider posting that and mark it the same way, and if you are still working upon things, leave an update.

Regards,
Brett