Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Numeration of Rows for different Values

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!

2 Replies
antoniotiman
Master III
Master III

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;