Jump to content

Reorganize Data For Time Series Analysis


Hellmut
 Share

This topic is 5672 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Thanks to your help, I have defined a simple database and entered the data. Now I need to reorganize the data into several timelines.

The data tracks violence in the lives of high officers of the English crown who held office between 1603 and 1763. The tables reflect events in the lives of the officers.

I would like to reorganize the data into timelines that list certain events annually such as

how many individuals are alive in any given year,

how many of them suffered a violent event of any kind in any given year,

how many knights, barons, earls etc. were alive in any given year,

how many knights, baron etc. had to suffer violence in any given year,

what kind of violent events occured how many times any given year, and

what kind of violent events occurred to knights, barons etc. in any given year.

FYI, I am enclosing the database.

I gather that calculating field values might be one way, especially if one creates a layout with annual values. Since I would have to define and program some 160 fields per timeline, I am sure that there will be a more elegant solution.

Also, I have no idea about the code required to calculate fields and would be grateful for general and specific tips about that as well.

Leviathan__s_Database.fp7_2.zip

Link to comment
Share on other sites

I believe you need an additional table of Years, with 160 records - one record per year. Then a single calculation field in the Years table will provide 160 different results. You will need to define relationships between the Years table and your other tables (more precisely, between the Years table and occurences of your other tables).

For example, if you have a relationship to a table occurence of Individuals defined as:

Years::Year ≥ IndividualsAlive::YearOfBirth

AND

Years::Year ≤ IndividualsAlive::YearOfDeath

then a portal based on this relationship will show the individuals that were alive in this year, and a calculation of Count (IndividualsAlive::IndividualID) will show how many. This is a relatively simply example - some of your other requirements are more complex.

I am not sure how you want to handle Individuals with no YearOfBirth/YearOfDeath. Some sort of calculated default should be put in place, I think. And, BTW, you really shoudn't have empty records...

Link to comment
Share on other sites

I see no IndividualID Number in any records in your Annual Events table. :wink2:

Yep, portal fills when you plug in an ID such as 372.

Edited by Guest
Link to comment
Share on other sites

Well. All I can tell you is that the portal is based upon Individuals (and so are the fields). And the Layout is based upon Annual Events. Your graph from Individuals to Annual Events is based upon Individual ID Number on both sides.

Annual Event::Individual ID Number is a number (indexed)

Individuals::Individual ID Number is a number (indexed, auto-enter serial)

And it produces the correct results but you have NO Individual ID Numbers in your Annual Event table!! They are blank. This isn't what you and Comment were discussing as far as I can tell but the relationship certainly isn't working without IDs - not on THIS table occurrence anyway!

Heck, this can't be the file. It doesn't even HAVE the calc Comment mentioned in Individuals. No calcs!

UPDATE: Okay calc is in Annual Events. But it's auto-enter (calc) number... and that relationships is still based upon the IDs.

Edited by Guest
Link to comment
Share on other sites

This topic is 5672 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.