Link .csv files if "repeat" has been used

Hi all. We have done a household survey with general questions (sanitation, water supply, etc) and then questions per each member of the house, including everything in 1 single form. We designed the survey using "begin repeat" and "end repeat" twice in a form.
I have downloaded the data using ODK Briefcase (last version) at it created 3 different csv files: one with the general questions related with each house, one with all the names of the people leaving in each house and the last one with the info (age, sex, education, etc) of each person leaving there.
I have seen that it created the two last forms with KEY and PARENT KEY in the head of some columns. I need help because I do not know how to link the three files in 1 single csv file to be able to extract data of each house.

Thank you so much for your help.

Welcome to the ODK forum, @Pere! We're glad you're here. When you get a chance, please introduce yourself on this forum thread. I'd also encourage you to add a real picture as your avatar because it helps build community!

The key is what links your data. Each data row in your general data will be a household. Each household has one or more members, so each row in your people data will have the unique ID of a household linking it to one of the rows in your general household data. This is a One-to-many data model in which an element of "household" may be linked to many elements of "individual", but a member of "individual" is linked to only one element of "household". You'll want to use a pivot table on the household data to summarize on the unique household identifier values. Then you can use something like a VLOOKUP to pull that summarized data (for example, count of members in each household) across and add it as additional columns in your household data table.

3 Likes

A post was merged into an existing topic: Introduce yourself here!

I asked the same question last week, vlookup helps :slight_smile:

Also, welcome to the community Pere

3 Likes