Joining of analytic and questionnaire data

To end up with a complete database table, the separately stored information of questionnaire (PSC coded) and analytical data (SSC coded) were joined together. In a first step, this was performed over a simple join with the PSC to SSC table, connecting both information (see chapter 1.2.4.2). Upon checking the number of identified pairs, discrepancies in the amount of joined subjects indicated a problem within the coding system.

Intensive manual search, identified the following problem. For a structured entering, the questionnaires were split in six parts (Moreno-Villanueva and Kötteret al., 2015). For each part the PSC has to be entered separately on the upload site. Manual checking of suspicious cases showed that the identifying codes were entered with typos, for equal subjects, during this procedure. The SQL system recognizes each newly entered, differing PSC, as a new subject and generated a new identity (row) in the database. For this reason, the questionnaire parts of one subject, can be located in different rows with varying subject code indications (Tab. 5.4).

The PSC to SSC translation table is no longer able to join the SSCs from the analytic data to the complete questionnaire information, now split in different rows. In further recruitment rounds, inconsistencies in the two letter code of the PSC appeared. They were either transposed, or typed differing for lower and upper case letters, leading to the generation of unreal new identities (Tab. 5.4).

PSC SSC Quest.1 Quest.2 Quest.3 Quest.4 Quest.5 analysis

1 0200123 12345 x x x X x

2 0200128 12346 x

3 02rt123 12347 x x x

4 02RT123 12348 x x

Table 5.4 PSC entering problem (adapted from Bauret al., 2015a)

Row 1 and 2 represent two entries, for the same subject, in the first recruitment round. When the second questionnaire was entered (Quest.2), the interviewer transposed the number 8 with a 3 in the PSC by mistake. The second, newly inserted code is unknown to the system, which therefore generates a new row with a new SSC. The new row reflects a new subject in the database. The same problem occurred, if letters indicating further recruitment rounds were transposed (rt -> tr), or indicated differently with lower or upper case letters. Although in this case (Row 3 and 4) a new subject is generated.

To solve this problem, specific workflows were established, joining the analytic and corresponding questionnaire information for each recruitment round and subject (Flow 5.1).

Subsequently, the subjects of the four recruitment rounds were grouped, and separated in MetaNodes (Fig. 5.2, D).

5.1.2.5.1 First recruitment

The identification code for the first recruitment round consists of two times zero “00” (see chapter 1.2.4.3). In cases where the number zero is replaced with the letter O, an identification of split questionnaire parts is possible. The subject identifying parts of the code stayed equal and were combined with KNIME. If typos happened in the subject identifying part of the codes (last three numbers) an assembling of split entries was not possible. A reliable method to identify typos is not available. Only speculations on the cause of a typo can be performed. For example if the number 7 is replaced with the number 1. Such confusions can result from imprecise hand writing. Subjects where only parts of the questionnaires were entered are documented and excluded from the analysis. A total amount of 3.1% faulty entries were thereby identified.

5.1.2.5.2 Re-sampling and Re-testing

For subjects in further recruitment rounds multiple entries were possible as explained before (see chapter 5.1.2.5). Cases offering inconsistent identification letters (see chapter 1.2.4.3) could be identified with the resting, unique and subject specific part of the code. For this purpose, a workflow was established writing all separately stored information of a single subject into one valid PSC (Flow 5.1).

Depending on the considered recruitment round, all belonging subjects were selected with a specific regular expression command. To determine the available amount of placeholders in a group (e.g. re, er, RE, ER), they were requested from the full amount of entered PSCs. In a second step all placeholders were exchanged with 00, leading to unique codes for each subject.

Questionnaire parts split in different rows were now combined for equal PSCs. The correct identifiers where added in the end with RE for re-sampling and RT for re-testing. A checkup system was implemented searching for multiple entered questionnaire parts. They were only overwritten if they appeared equal. The combination of questionnaire parts from different rows leads to the loss of the generated SSCs. They are of course not equal, because they were newly generated for each of the multiple entered subjects (Tab. 5.4). A direct join of data using the SSC is now invalid. From now on, only a join with the PSC code results in the correct combination of analytic and questionnaire data, for re-sampling and re-testing subjects.

An important information for further analysis is the subject specific time between the first and following recruitment rounds. Therefore, an additional column was attached to the database, containing the time information between the different recruitment rounds in days. For the calculation of the time span, the date of blood donation was used.

In the testing phase all six parts of the questionnaires were entered again. During the re-sampling procedure only two parts were entered again, as in this short time period changes are not expected (Bürkleet al., 2015). To receive the whole information for all subjects, the missing parts were completed, with those, entered in the first recruitment round.