creating groupnumbers and group item numbers.. how?

[amien]

i have a table with 3 column: datetime, latitude, longitude.

now, i want to add 2 extra columns:

1) IF Previous(latitude) = latitude and Previous(Longtitude) = longtitude ==> start new routeID

2) IF Previous(latitude) <> latitude and previous(longitude) <> longtitude ==> create an routeitemID

So, this will be added to the current table:

1 , 1

1 , 2

1 , 3

1 , 4

2  , 1

2  , 2

3  , 1

3 , 2

etc

How can i do this?

[#2]

If you don't have a row ID or key field based on the order, you can use the above() function to return the previous value for longitude, latitude and your new route id

so your first column's expression would look like

=if(above(latitude) = latitude and above(longitude) = longitude, 1,above(routeid) +1)

Erica

[rwunderlich]

I'm not sure I understand. Do you want a new routeID everyt  time latitude changes and a routeItemID for each longitude within the latitude group? If so, try

autonumber(latitude) as routeID,

autonumber(longitude, latitude) as routeItemID

-Rob

[amien · Accepted solution]

@Erica, Above is an chart expression, i need this in load

@Rob, I dont have a routeID and routeitemID .. i want to create one, based on change of latitude and longitude

LOAD

    *,

    IF(PREVIOUS(Longitude) = Longitude AND PREVIOUS(Latitude) = Latitude,RowNumber,PEEK(RouteID)) AS RouteID,

    ;

i can use this one for creating a RouteID (or do you have an better alternative?)  But how to create RouteItemIDs?

I can use autonumber to create a clean routeID afterwards

[amien]

i will just use RowNo() as RouteItemID