Hello,
I struggle with joining two tables containing wildcards. Here is sample table:
| ClientID | SubID | Name | Category | Stream | CombinedID |
| 2346 | String1 | Name1 | Category1 | Stream1 | 2346String1 |
| 2346 | String2 | Name1 | Category2 | Stream2 | 2346String2 |
| 2378 | String3 | Name2 | Category2 | Stream2 | 2378String3 |
| 2378 | String4 | Name2 | Category3 | Stream3 | 2378String4 |
| 2470 | * | Name3 | Category2 | Stream3 | 2470* |
| 2646 | * | TBD | Category3 | Stream3 | 2646* |
| * | * | TBD | Other | Other | ** |
What I need basically:
- If there is defined ClientID and SubID, use this as unique ID when joining towards final data table
- If there is Defined ClientID only, join only based on ID (There are multiple SubIDs that e.g. 2470 can get, but it will always be linked to same Name, Category and Stream)
- For other, not defined matches in the table (No hard linked Client ID and SubID) use Values "TBD; Other; Other".
I need this to implement into the load script.
The sample table is taken from Excel, so it can be dynamically changed in case some new relationship needs to be defined. Full dataset is growing steadily and currently has over 160k rows with at least 30 fields. Each month there is approximately 2000 more rows. This dataset contains the ClientID and SubID (which combination I've wanted to use as a primary key shown in the table as CombinedID). With this approach I got stuck because of the wildcard.
I've also tried pure joining (with "*" or with empty cells), but that ended in a lot of empty values.
I've also tried to create Cartesian product for each combination of ClientID and SubID, but that ended in over 18 million rows.
I've also tried Rob Wunderlich's approach, but that is pretty limited for my data set (as expression length can be only over 8k characters).
Thank you for any help or pointing towards direction
