Rank or RowNo to get the row number based on previous dimensions

[wanyunyang]

Hi all,

I have a sample table like below:

Department, Group, Name

Sales, 1, A

Sales, 1, B

Sales, 2, C

Finance, 1, D

I want to assign row number to Name, but based on Department & Group, so the expected output would be:

Department, Group, Name, RowNumber

Sales, 1, A, 1

Sales, 1, B, 1

Sales, 2, C, 2

Finance, 1, D, 3

Is this possible? Thanks in advance!

[Chanty4u]

Try this example 

Data:

LOAD * INLINE [

    Customer, Product, Sales

    A, X, 100

    A, Y, 200

    A, Z, 300

    B, X, 150

    B, Y, 250

    C, X, 120

    C, Y, 180

];

Result:

LOAD

    *,

    RowNo() as RowNumber

Resident Data

Order By Customer, Sales Desc;

DROP Table Data;