create number

[#1]

Hi all,

i am working with a source table that contains history data. It contains for one specific object multiple records.

Each record corresponds to a change of the object that happened in the past.

I need to numerate every history record for each object.

The source table looks something like this:

ObjectID	HistoryID	Change	RecordCreated
1	11	some text here...	2013-01-03 08:34:12
1	12	some text here...	2013-01-04 11:33:13
2	13	some text here...	2013-01-13 07:56:23
1	14	some text here...	2013-02-11 10:11:41

Now i'd like to create a new field in this table - let's call it JournalNumber, whould basically count the records for every object sorted by RecordDate asc:

ObjectID	HistoryID	Change	RecordDate	JournalNumber
1	11	some text here...	2013-01-03 08:34:12	1
1	12	some text here...	2013-01-04 11:33:13	2
2	13	some text here...	2013-01-13 07:56:23	1
1	14	some text here...	2013-02-11 10:11:41	3

Any idea, how this can be done ?

Thanks a lot!

[swuehl]

Try as additional field in your load script:

LOAD

     ...

     autonumber(HistoryID, ObjectID) as JournalNumber,

     ...

edit: to get your JournalNumber field ordered by Date, you may need to use an order by in your load statement.