RowNumber () over (Partition by) in SQL Server implemented in Qlikview

Often there is a requirement that when a field has a lot of duplicate values, but the values in the B field are unique after grouping by a field, so select a, B fields do not know which value in the B field, so when the A field repeats, you want to take the value of the B field, The row of data that corresponds to the maximum minimum value in the C field.

SQL Server can be implemented as follows, Row_number () over (Partition by A Order by C) ID, and then take id=1.

In Qlikview you can do this in the following ways:

The data is prepared as follows:

Sales:loadnum (Salesmanid) as Salesmanid,num (ManagerID) as Managerid,date (Date) as Dateinline [Salesmanid, ManagerID, DATE21, 190, 2013-05-0621, 201, 2014-05-0622, 652, 2013-05-0623, 813, 2013-05-0623, 915, 2014-01-0623, 961, 2014-05-06];

Sales table Salesmanid is the salesperson ID, in different times salesmanid corresponding to different managerid, according to demand should be the salesperson recently owned ManagerID.

Salehierarchy:loadsalesmanid,if (Peek ("Salesmanid") =salesmanid, Peek ("RowNumber") + 1, 1) as Rownumber,managerid, Dateresidentsalesorder bysalesmanid,date desc;drop Table Sales;

Note: Be sure to order by Salesmanid and date two fields.

As shown, taking rownumber=1 is the desired result.

So the code can be written like this:

Salehierarchy:loadsalesmanid,if (Peek ("Salesmanid") =salesmanid, Peek ("RowNumber") + 1, 1) as Rownumber,managerid, Dateresidentsaleswhereif (Peek ("Salesmanid") =salesmanid, Peek ("RowNumber") + 1, 1) =1order bysalesmanid,date Desc;drop Table Sales;

If you do not know how to use peek please see Help or message exchange.

RowNumber () over (Partition by) in SQL Server implemented in Qlikview