Forum Discussion
Solved
How to translate data into a longitudinal database
Hello! I've been stuck on this problem for hours. I'm trying to convert data into a longitudinal data-set, and have built an equation that almost does the trick. BTW if there is an easier way to do this, I would love to learn it. In the attached document I've made 3 tables. Table 1 is the data you start with, Table 2 is the table I want, and Table 3 is the table I have. I've highlighted the parts that are broken, the rest I have figured out in my actual file.
My big problem is that I'm searching on two variables, person_id and quiz_year, and I cant figure out how to make excel search through a range. For example, if a period_id of 1 has 3 quiz_years, my formula will not look beyond the first quiz. I think I'm just rambling now...Please take a look at the attached document. I've done my best to demonstrate the problem.
Thank you for any help or advice you might have.
Update: What I really need to know is how to use match/index to search look at two columns of data, person_id and quiz_year. I need the formula to get all the way down to the correct quiz_year and THEN begin checking for the correct person_id. My order of operations is wrong and I dont know how to fix it.
Load Table1 into the PQ editor.
Select columns person_id and quiz_year. Right click -> unpivot other columns.
Select column Attribut. Right click -> replace value -> enter quiz_reponse and qr.
Select column quiz_year. Click on the ABC icon in the header and change the data type into Text.
Add user defined column. Type this formula: "year_"&[quiz_year]&"_"&[Attribut]
Select columns quiz_year and Attribut. Right click -> Remove.
Select column Benutzerdefiniert. Transform -> Pivot column. Select Value column: Wert.
Select more options: Do not aggregate.
The wording might differ because I use a non-english version and tried to translate.
Besides "right click" the commands are also available in the ribbon.
Thanks for the response. I imagine I need to install power query for this work? I don't know if you meant to send me that file. It has a new table, but doesnt show how you got that new table. I did find the Power Query feature and am playing with it now, but cant figure out how to duplicate what you did.
Load Table1 into the PQ editor.
Select columns person_id and quiz_year. Right click -> unpivot other columns.
Select column Attribut. Right click -> replace value -> enter quiz_reponse and qr.
Select column quiz_year. Click on the ABC icon in the header and change the data type into Text.
Add user defined column. Type this formula: "year_"&[quiz_year]&"_"&[Attribut]
Select columns quiz_year and Attribut. Right click -> Remove.
Select column Benutzerdefiniert. Transform -> Pivot column. Select Value column: Wert.
Select more options: Do not aggregate.
The wording might differ because I use a non-english version and tried to translate.
Besides "right click" the commands are also available in the ribbon.
