Forum Discussion
Combining/Merging columns with different data and quantity
Hello I am currently working on a project which requires me to combine 2 files. the examples of the 2 files are shown below.
File 1
| PO tracker | PR tracker | data |
| 12345678 | 11111A | 12 |
| 87654321 | 22222A | 13 |
| 11111111 | 33333A | 8 |
| 22222222 | 44444A | 7 |
| 33333333 | 55555A | 6 |
| 44444444 | 22222B | 5 |
| 55555555 | 22222R | 4 |
| null | 88888L | null |
| null | 22222T | null |
File 2
| Tracking PO | Tracking PR | value |
| 66666666 | 66666U | 55 |
| 77777777 | 99999J | 11 |
| 11111111 | 33333A | 8 |
| 22222222 | 44444A | 7 |
| 33333333 | 55555A | 6 |
| 44444444 | 22222B | 5 |
| 55555555 | 22222R | 4 |
| 88888888 | 00000N | 32 |
The columns needs to be merged to end up with looking like this.
| PO tracker | Tracking PO | PR tracker | Tracking PR | data | value |
| 12345678 | 11111A | 12 | |||
| 87654321 | 22222A | 13 | |||
| 11111111 | 11111111 | 33333A | 33333A | 8 | 8 |
| 22222222 | 22222222 | 44444A | 44444A | 7 | 7 |
| 33333333 | 33333333 | 55555A | 55555A | 6 | 6 |
| 44444444 | 44444444 | 22222B | 22222B | 5 | 5 |
| 55555555 | 55555555 | 22222R | 22222R | 4 | 4 |
| null | 88888L | null | |||
| null | 22222T | null | |||
| 66666666 | 66666U | 55 | |||
| 77777777 | 99999J | 11 | |||
| 88888888 | 00000N | 32 |
hi KJ_YG
You need to manage the relationship between the primary keys in the table.
To do that, go to model view and add a new relationship like below:
Once you do that now you can add a matrix in the order you need to get the output.
I simulated your scenario and got this:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
hi KJ_YG
You need to manage the relationship between the primary keys in the table.
To do that, go to model view and add a new relationship like below:
Once you do that now you can add a matrix in the order you need to get the output.
I simulated your scenario and got this:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thany you so much for your help. However, it would be better if i were to create a 1 to 1 relationship between my PO tracker and tracking PO values. Why is it not possible to create a 1 to 1 relationship between them?
It is possible to create 1:1 relationships. You can see it in my screenshot above I have created 1:1 with your sample data.
Are you not able to change it? Then it could be that your data may be not have unique values in one/both of the tables.
