Hi,
I have a visit based study where there are multiple observations for a specific subject per visit. I have defined a sequence map (e.g., 1-10 sequence belongs to visit 1). Is there a way to create a data grid by joining the tables not based on visits sequence but by subject ids and any other column?
The reason is because if I try to join the two table where in one table I repeated visits and the second table has only one visit per subject, then the resulting grid has only visit joined and all the other rows are appended as if they belong to a different visit, which is not true.
In database terminology, I am talking about an outer merge of two tables based on subject id and visit ( not by subject id and visit sequence number).
Can anybody help here?
|
| |
| Jon (LabKey DevOps) responded: |
2018-09-19 22:24 |
Hi Edward
Can you give us an example of what you're working with?
Are you trying to join two different datasets or a dataset and another table, like a list?
Can you give us an example of data from the two tables, your SQL query, and the desired results you are expecting to see?
Regards,
Jon |
| |
| Edward responded: |
2018-09-21 01:44 |
Hi Jon,
Thanks for replying. I am trying to create gridview by joining columns from two different datasets.
In these datasets, one has few columns while the other one has upto 150 columns. I noticed that in these 150 columns a set of 15 columns are repeating and so I thought it would be best to transpose the repeating columns into rows, which will significantly reduce the number of columns but it will also mean to have duplicated values for Visits. Due to this I made visits sequence (such that visit 1 = 1.00-1.99, 2 = 2.00-2.99 etc.).
As an example consider the attached screenshot S1 where I have joined the transposed dataset on another dataset. If you can see I get Name, age and sex columns empty because the database is doing join based on Visit_Sequence and id, even if Visit_Sequence values belong to the same visit. Ideally, I would like to get a resulting table similar to the attached screenshot S2.
Your help is highly appreciated. Thanks! |
|
| |
| Jon (LabKey DevOps) responded: |
2018-10-05 14:51 |
Hi Edward,
Thanks for providing the images. If you're trying to do what is in screenshot two, you could just create the following query in the schema browser within the study schema:
SELECT table2.id,
table2.Med_No,
table2.Name,
table2.Time,
table2.Quantity,
table1.Name AS Table1Name,
table1.Age,
table1.Sex
FROM table2
JOIN table1 ON table2.id = table1.id
But if you're trying to use the fields that are nested within the grid view, these rely on the combination of the participant Id number and the specific visit value together.
Regards,
Jon |
| |
|
|
S1.png
S2.png