How do I mark missing data in OpenOffice Base?

[Kaido9449]

Hello to all,

sorry for my grammar, but I am not a native English speaker.
Let´s imagine that I have an excel table, that contains patient data. We have patient A1 who has arterial hypertension (yes=1 no=0), myocardial infarction (yes=1 no=0), thoracic aortic aneurysm (45 mm). But for patient A2, all information is missing, so that I write n.a. (not available) for all information.

If I were to convert the excel table into Open Office Base, it would write everything as a text and say n.a. for the missing values.
Since I will be doing Kaplan Meier Survival curves etc. , how do I mark missing data in Open Office Base? Could I just leave it open and it would not be used for analysis later on?

What is the smartest way to do this? It is my first time using Base.

Hope you can help me

[UnklDonald418]

Relational database design is not intuitive, there is a definite learning curve. It does require intimate knowledge of the data, rules, formulas, procedures and desired outcomes. From the information you supplied, it would be difficult to give you specific recommendations.

Information is retrieved from data stored in database tables using queries written in SQL the language most databases understand.

If you included n.a. in the cells when you copy the spreadsheet data to a database table, for instance "PatientTable" then a query something like the following

Code: Select all

SELECT  * FROM "PatientTable" WHERE "arterial hypertension" <> 'n.a.'

would generate a list of Patients excluding any where the field "arterial hypertension" contains the string 'n.a.' Or

Code: Select all

SELECT  * FROM "PatientTable" WHERE "arterial hypertension" = '1'

would limit the list to those Patients that have arterial hypertension.

Database fields can also have a special state where there is no information stored in them, called NULL
If instead of including n.a. in the spreadsheet you left those cells empty, when you copy the data into the database table the Copy Table dialog will put nothing in those fields, in other words those fields would be NULL. The following query would deliver the same limited list of Patients as the first query.

Code: Select all

SELECT  * FROM "PatientTable" WHERE "arterial hypertension" IS NOT NULL

There is another option, you could copy both "arterial hypertension" and "myocardial infarction" into Boolean fields. Boolean fields can be TRUE (Yes=1), FALSE(No=0) and NULL(empty or unknown).

Any of the approaches would probably work, but with our limited knowledge of all the factors it would be difficult to say if one is better than the other.
However, the constraints on Boolean fields often makes the data entry less prone to silly errors. For instance what happens if 11 gets entered in a string field for "myocardial infarction"?
Boolean fields on a Base form can be controlled with either check boxes or option buttons.

[Villeroy]

As a general rule of thumb, all columns should be marked as not nullable (except some column for unsystematic extra comments). This way the database refuses to store any incomplete row and later data analysis does not require consistency checks of the kind we see in every spreadsheet =IF(A1="";"missing";A1*1.18)