Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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;
.
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;
.
See attached
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