Assign sequential number (001, 002, 003) for each unique name field?

[#1]

Hi,

   I have a name field  COM_NM  .To each distinct COM_NM we need to assign sequential numbers --001,002,.......till the count of distinct COM_NM.

Can anybody give me exp:

Thanks

[swuehl]

Is your field COM_NM maybe a float, formatted as integer or text? Like a timestamp formatted as date? This would explain what you are seeing.

[swuehl]

Please post at least your script.

[#4]

COM_NM is ECISName ...follow the screenshot

[GabrielAraya]

I tested with the following script ...

lOAD

    *,

    AutoNumber(COM_NM,'COM_NM_Seq')        as COM_NM_Seq;

LOAD * INLINE [

    COM_NM, OtherField

    A, 12

    A, 23

    B, 543

    C, 34

    C, 34

    C, 56

    D, 67

    D, 5

    E, 45

    E, 34

    E, 56

    E, 67

    F, 67

    G, 98

    G, 87

];

For the value A .. I have the same sequential number 1 .. in my example.

[swuehl]

COM_NM is ECISName ...follow the screenshot

I can't the see full text of that field, i.e. can't see if they differ at the end.

Can you post a screenshot of a listbox showing that value?

And, again, the script that you are using to create the table containing your field and the autonumber.

[#7]

script:

Load DISTINCT

num(autonumber(ECISName,'sALPOSNR'),'000')as sALPOSNR

Resident Target

order by ECISName;

I checked the names they all are same...even at end...........

[swuehl]

Still unable to reproduce your issue.

What do you get using

=Count(Distinct ECISName)

in a textbox?

And still I am waiting for a screenshot of your listbox (a list box is only showing distinct values, if you see the same ECISName multiple times, these values differ in numeric representation.

Where does ECISName originally come from?

[sasiparupudi1]

Hi umanukala

Please try the following script and hope that it meets your requirement.

Table1:

LOAD * INLINE

[

ECISName ,Year,Month,  Amount

American Equity Group,2015,5,100000

American Equity Group,2015,6,600000

American Equity Group,2015,7,400000

American Equity Group,2015,4,100000

Armitage Trading Company,2015,5,200000

Armitage Trading Company,2015,4,200000

George M Consulancy Company,2015,5,560000

George James Trading Company,2015,4,200000

];

NumMap:

Mapping LOAD Distinct ECISName as DistinctECISName,Num( AutoNumber( ECISName , 'COM_NMs' ) , '000' ) as sALPONR      Resident Table1;

NoConcatenate

FinalTable:

LOAD *,ApplyMap ('NumMap', ECISName) as ALPONR Resident Table1;

drop Table Table1;

[#10]

I have 20,000 ECISName

[swuehl]

umanukala wrote:

script:

Load DISTINCT

num(autonumber(ECISName,'sALPOSNR'),'000')as sALPOSNR

Resident Target

order by ECISName;

I checked the names they all are same...even at end...........

Is this the exact script you have used? No other fields in that table, i.e. I assume it's a data island not connected to other tables?

Then, I think what you are seeing is that your screenshot does not show the relation between ECISName and sALPOSNR, we are seeing a cross product of these fields.

Try

Load DISTINCT
ECISName as ECISNameTest,
num(autonumber(ECISName,'sALPOSNR'),'000')as sALPOSNR

Resident Target

order by ECISName;

And then check the results using only these two fields in a table box.

If above is not showing the full script for the table load, please update.