Hi guys here's what I'm trying to accomplish....
My data structure Table1:
ID#| Capability.1 | Capability.2 | Capability.3| .... 37 total Capability.# columns
97 | Crawl | Walk | Run |
98 | Crawl | null | null |
99 | Crawl | Walk | null |
My data structure Table2:
Capability | Vehicle1Score| Vehicle2Score| Vehicle3Score|
Crawl | 4 | 1 | 5 |
Walk | 3 | 1 | 5 |
Run | 2 | 0 | 0 |
If a ID# requires crawling, walking, and running I would like to have the scores of Vehicles 1-3 merged with the record on how well they can crawl, walk and run. For example:
ID#| Capability.1 | Capability.2 | Capability.3| Vehicle1CapaScore | Vehicle2CapaScore | Vehicle3CapaScore|
97 | Crawl | Walk | Run | 9 [4+3+2] | 2 [1+1+0] | 10 [5+5+0] |
98 | Crawl | null | null | 4 [4+null+null] | 1 [1+null+null] | 5 [5+null+null] |
99 | Crawl | Walk | null | 7 [4+3+null] | 2 [1+1+null] | 10 [5+5+null] |
I've gotten as far as using merge queries (LeftOuterJoin on Table1[Capability.#] = Table2[Capability]) to bring the Table2 Capability and Vehicle Scores into Table1 for each Capability.# column (Total of 37 merged queries). However, I have no idea how I would get the 37 separate merged queries to sum and populate a score for the Vehicle1CapaScore column.
If it's helpful I can write a query in SQL server to demonstrate what I'd like to accomplish; however, I'm not proficient enough in R to do the same. Please let me know if this makes sense and you can help.