topic Re: Keep only latest record in QlikView

Keep only latest record

safik003 — Sat, 21 Sep 2019 19:40:29 GMT

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

Re: Keep only latest record

Vegar — Mon, 23 Sep 2019 07:21:34 GMT

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;

Re: Keep only latest record

AVINASH22 — Mon, 23 Sep 2019 03:41:27 GMT

Use aggr function taking expression as Yearmonth with dimension employee

Re: Keep only latest record

deshikas — Mon, 23 Sep 2019 05:23:55 GMT

See attached

Re: Keep only latest record

Brett_Bleess — Tue, 01 Oct 2019 16:30:08 GMT

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