Hello all
I have a question. I have two tables with different information. One table denotes material consumption, the other table denotes material orders.
The problem is that these are pulled from different systems and have no match. But I have a matching table which could connect them accordingly.
Table 1
| Date | Location | Name_1 | Table_ID1 | Amount_1 |
| 01.02.2022 | A PB 1 | Name 1 | KZ0312 | 2 |
| 01.02.2022 | A PB 1 | Name 2 | KZ0131 | 4 |
| 01.02.2022 | A PB 1 | Name 3 | KZ0132 | 2 |
| ... | ... | ... | ... | ... |
Table 2
| Date | Location | Name_2 | Table_ID2 | Amount_2 |
| 01.02.2022 | A PB 1 | Name 1_X | GL511642 | 22 |
| 01.02.2022 | A PB 1 | Name 2_X | GL06513584 | 42 |
| 01.02.2022 | A PB 1 | Name 2_X | GL43151 | 21 |
| ... | ... | ... | ... | ... |
Matching Table
| Table_ID1 | Table_ID2 | Name_1 | Name_2 | Product_Name |
| KZ0312 | GL06513584 | Name 1 | Name 2_X | Product 1 |
| KZ0131 | GL511642 | Name 2 | Name 1_X | Product 2 |
| KZ0132 | GL43151 | Name 3 | Name 3_X | Product 3 |
| ... | ... | ... | ... | |
It is important to know that there are items in table 1 which are consumed but which have not been ordered (stock), so they do not appear in table 2.
The matching table has all the items listed.
The aim should be to get the following table:
Desired result
| Location | Product Name | Amount_1 (used) | Amount_2 ordered |
| A PB 1 | Product 1 | 2 | 42 |
| A PB 1 | Product 2 | 4 | 22 |
| A PB 1 | Product 3 | 2 | 21 |
| ... | ... | ... | ... |
