Hi Community,
I am facing a challenge that I would like to share with everyone.
Imagine we have a FACT table like:
FACT:
| A | B | C | D | E | F | G |
| 1 | 8 | 9 | 3 | 4 | 10 | 1 |
| 9 | 2 | 7 | 2 | 2 | 2 | 6 |
| 3 | 3 | 1 | 4 | 4 | 6 | 6 |
| 4 | 4 | 2 | 3 | 7 | 5 | 6 |
And also a "Mapping" Table like:
| A | B | C | D | E | F | G | OUTPUT |
| 1 | | | 3 | 4 | | | AA |
| | 2 | | | | | 6 | BB |
| | 3 | | | 4 | | | CC |
| 4 | 4 | | | | 5 | | DD |
Now, I need to retrieve the OUTPUT values in the FACT table. Considering that "blank" values in the mapping table means "regardless the value in the fact table".
The only solution I can think about is using a LOOP to create "all possible combinations of keys" or "all possible mapping tables" since the key to join both table is unknown. In other words, The key might be , A, AB, ABC, ABCD, .... etc. That means we have 7 factorial = 5040 possible keys.
FYI, my real scenario consist in a table with +50k rows and 10 mapping fields (about 4M possible combinations of keys). And a fact table with +50M rows.
Hope the explanation is clear enough.
Any ideas?
Thank you.
