Assign Row Numbers

[jamelmessaoud]

Hi guys,

I have some data like below

Row No.	Type	LoadID	ConsID	JobID
0	Master	600801	1090480	625720
1	Leg	600801	1090480	625910
2	Leg	600801	1090480	625911
0	Master	694092	59682135	644897
1	Leg	694092	59682135	644901
2	Leg	694092	59682135	644902
0	Master	710724	59705448	647807
1	Leg	710724	59705448	647971
2	Leg	710724	59705448	647972
3	Leg	710724	59705448	647973

Any idea how I would go about assigning the row numbers like the above example?  I have played about with the Autonumber function but I cannot figure this one out!

The type Master is always 0 and then the others I would like to autonumber.

Help!

Thanks
J

[jamelmessaoud]

I think the grouping can be the consid or loadid because this will be the same for the group of records if that makes sense? Perhaps i can auto number when the consid changes?

[vamsee]

Hi Jamel,

Apologies for that then. Try this

Source:
LOAD
*,
ConsID &'|'& JobID &'|'& Type as Key;

LOAD * INLINE [

Row No., Type, LoadID, ConsID, JobID
0, Master, 600801, 1090480, 625720
1, Leg, 600801, 1090480, 625910
2, Leg, 600801, 1090480, 625911
0, Master, 694092, 59682135, 644897
1, Leg, 694092, 59682135, 644901
2, Leg, 694092, 59682135, 644902
0, Master, 710724, 59705448, 647807
1, Leg, 710724, 59705448, 647971
2, Leg, 710724, 59705448, 647972
3, Leg, 710724, 59705448, 647973
];



Temp_Table:
Left Join (Source)
LOAD
Key,
IF(Previous(Type)='Master',1,IF(Type<>'Master',Peek(Row_Number)+1, 0)) as Row_Number
Resident Source;
DROP Field Key;