scenario- is the timestamp the primary key?

[josephinetedesc]

This is the scenario: (a bit like people running a race around a track)

Let there be 10 people, P1, P2, P3 to P10

Let there be 2 tracks, T1 and T2

On each track - there are 3 locations which are timestamped L1,L2, L3.

Some people can go through the track twice or finish half way through etc - ie they all get to the 3rd Location

Problem with this scenario is that it is assumed that the people must go sequentially through the locations but this is not necessarily the case.

The information to come out of this scenario - the time needed for a person to run their individaul race. 

The infromation comes out of the system as rows thus:

Person1     Track1     Location1      Timestamp

Person2     Track2     Location2     Timestamp

Person 3     Track1    Location2     Timestamp

Person 2     Track 3    Loation 2     Timestamp

Person 1     Track1     Location1     Timestamp

How should I display the information in order to get the time taken from one Location to the other?

I am thinking something like this:

          Location1/Track1     Location2/Track 1     Location 3/Track 1          location 1/Track2     location2/Track2     location 3/Track3 etc

P1     timestamp                 

P1     timestamp    

P2                                                                                                                                                                timestamp                 timestamp       

P3                                            timestamp

What should I do here -

something ike      Person     Person/Location/Track      Location/Track     timestamp

Trial and error - is not working!

Tried to do it in a database - to get the logic working ... (as I have explained it - it has given me another idea - trying to make the following table:

Track/Location   person     timestamp

1 1                      P1               timestamp

2 2                        P2             timestamp 

1 2                         P3              timestamp

3 2                         P2               timestamp

1 1                         P1                timestamp

So my pivot table could show the table as I want ... with Track/Location being the columns and the dimension being Person and the timestamp being the value.

Any thoughts?  I think my orignal idea of the timestamp as a primary key - dosen't work, although a person cannot be in 2 locations at the same time!

Thank you and I will try and mock up a spreadsheet/qlikview document.

Jo

Message was edited by: Josephine Tedesco The total is obviously incorrect - but I think I would need to do some set analysis here .... ie for each person get the time that has passed from one Track/Location to the next Track/Location.

[swuehl]

Some sample data in a sample app would be helpful.

You said that a person can run a track multiple times, visiting a location in a track multiple times.

So I think it's hard to use e.g. a pivot table with dimensions Location / Track to visualize this sequence of timestamps.

I think it would be better to use time as dimension, maybe something like a Gantt chart with Persion and Track & Location as grouped dimensions.

Or maybe a scatter chart with connected dots.

Or use a table / pivot table and show average durations between locations (I still would not show the timestamps here).

[josephinetedesc]

!