topic Re: Numeration of Rows for different Values in App Development

Numeration of Rows for different Values

Fri, 22 Sep 2017 18:33:07 GMT

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!

Re: Numeration of Rows for different Values

antoniotiman — Fri, 22 Sep 2017 18:48:11 GMT

Hi Jesus,

may be this

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

From Table;

Regards,

Antonio

Re: Numeration of Rows for different Values

sunny_talwar — Fri, 22 Sep 2017 18:54:15 GMT

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;