Hi to Achieve column ID

[alvinford]

Hi All,

I have the following data i.e. Call , Number and Name.

Based on the combination of Number and Name, need to derive the ID column.

The Number may have different names associated with it.

if the Number has only one Name the ID should be 1.

if the Number has more than one name the ID should be incremented. Example as highlighted in Blue below.

Please add your suggestions to achieve this.

Class	Number	Name	ID
1	801920170706	Sarinya	1
1	801920170706	Sarinya	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	850620170918	ANGSAYA	1
1	850620170918	CHALUAY	2
1	850620170918	CHALUAY	2
1	850620170918	WEERA	3
1	850620170918	WEERA	3
1	854820170815	Inta	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2

[ogautier62]

Hi,

make a sheet with Number, Name in dimension

and ID will be given by rowno()

regards

[alvinford]

Hi Olivier,

Thank You for your reply. I works fine in the front end.

Want to achieve this in the script. Sorry didn't mention this in my post.

Your help is appreciated.

Thanks You,

Alvin

[ogautier62]

test:

LOAD  Number,Name FROM   'your file'  ;   

load *, if(peek('Number') <> Number,1, if(peek('Name') <> Name,peek('rang')+1,peek('rang'))) as ID resident test order by Number,Name;

regards

[alvinford]

Hi Olivier, 

It works perfectly.. thank You ... 

Appreciate your help .. 

will mark it as correct answer .. 

[MarcoWedel]

Hi,

another solution might be:

LOAD Autonumber(Name, Number) as ID
FROM ...

hope this helps

regards

Marco