Numeration of Rows for different Values

[#1]

Hello,

I need to add a row numerion for each different value in a column. I've been looking for some kind if cicle to apply in Rowno() in a cicle, but I havent been able to find anything like that, maybe I am looking it the wrong way. The table I have looks something like this:

Client  Country  Value

Client1  US         100

Client2  US          300

Client3  CO         400

Client4  CO         100

Client5  CO          200

Client6  MX          500

...

Rowno() will only give me a row number for each client, but what I need is to evaluate each country and assign a number according to that Country, and start over every time the country changes. The result table would look like this:

Client  Country  Value  ClientCodePerCountry

Client1  US         100      1

Client2  US         300      2

Client3  CO         400     1

Client4  CO         100     2

Client5  CO         200     3

Client6  MX         500     1

...

The real data has about 500 to 1000 rows of different clients per Country, so I am looking for a somekind of while statement in a new table that does the evaluation and then returns the "ClientCodePerCountry".

Thank you in advance!

[antoniotiman]

Hi Jesus,

may be this

LOAD *,Autonumber(RowNo(),Country) as ClientCodeperCountry

From Table;

Regards,

Antonio

[sunny_talwar]

If the amount of data is large, I would suggest to use Previous function to do this as it tends to give better performance then AutoNumber. If the data is small, go with antoniotiman's solution

Table:

LOAD * INLINE [

    Client,  Country,  Value

    Client1,  US,        100

    Client2,  US,          300

    Client3,  CO,        400

    Client4,  CO,        100

    Client5,  CO,          200

    Client6,  MX,          500

];

FinalTable:

LOAD *,

If(Country = Previous(Country), RangeSum(Peek('ClientCodePerCountry'), 1), 1) as ClientCodePerCountry

Resident Table

Order By Country, Client;

DROP Table Table;