2 Replies Latest reply: Sep 22, 2017 2:54 PM by Sunny Talwar RSS

    Numeration of Rows for different Values

    Jesus Lopez

      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
          Antonio Mancini

          Hi Jesus,

          may be this

           

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

          From Table;

          Regards,

          Antonio

           

          • Re: Numeration of Rows for different Values
            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 Antonio Mancini'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;