Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
@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
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
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
@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
i will just use RowNo() as RouteItemID