Hello gurus,
I have run into an issue regarding our data and I'm not quite sure how to fix it in the best / most efficient way, so hopefully you will be able to offer your support.
The issue is that we have a dimention table that we use / have used for years, but now some of these values have changed and now you might say, then change the values in the table, but this is where the problem is. If I change the values in the data, all of my old data will have the new values and not the value which it had "back in the day".
| Zip Code | Store |
| 1 | C |
| 2 | B |
| 3 | A |
| 4 | A |
| 5 | B |
| 6 | A |
This is a basic lookup table, where Zip Code is unique and it returns the store that belongs to this Zip code.
When looking at the table in 2023, it now looks like this: (A new store has opened and some of the Zip codes, now belong to the new store "D")
| Zip Code | Store |
| 1 | C |
| 2 | B |
| 3 | D |
| 4 | D |
| 5 | B |
| 6 | A |
But I don't want to loose the data integrity of what it looked like back in the day.
So now on to the solution, I was thinking of just adding a "Year" column and when linking the tables, then link by both Zip Code and Year. For this solution I see two ways of doing it:
| Zip Code | Store | Year |
| 1 | A | 2022 |
| 2 | B | 2022 |
| 3 | C | 2022 |
| 1 | D | 2023 |
| 2 | B | 2023 |
| 3 | C | 2023 |
Problem here is, now the Zip Code column is no longer unique. The other option is:
| Zip Code | 2022 | 2023 |
| 1 | C | C |
| 2 | B | B |
| 3 | A | D |
| 4 | A | D |
| 5 | B | B |
| 6 | A | A |
Now we are back to the Zip Code, being the unique table, but the problem here is that I can't merge tables based on a changing column header.
Do you know of a solution for this problem?
I have the Year and Zip Code column in both tables already.
Thanks in advance,
B
