Joining Dimension Tables

Question

Hi,

I need to parse a long string with multiple codes that I need to join with dimension tables in order translate the codes to digestible strings. I've created a workflow to do this, but it requires a join for every dimension and across the several strings, that would be over 50 joins! Is there a more elegant way to do this? I've attached my sample workflow that is much more simplified with 3 dimensions, but the idea is the same.

Dimension Examples.yxmd

trickydigger · Answer

Because when you join on just the store code, it isn't matching the rest of the dimensions correctly. See bellow. The State code doesn't stranslate to the full state name because it's joining on just the correct relationship to the store number to store name. The other dimensions are indpendent

rzdodson · Answer

@trickydigger can you clarify here? I am taking a look at your workflow, and there should not be a reason why you would have to set up that many joins. Assuming you are wanting to functionally associate that tracking code to the store number it is coming from, you should be able to utilize a Regex tool (see below) to parse out the components of the tracking code, and then attach that table back to your reference table to produce the outcome.

Nevertheless, I am positing a potential solution for you. Depending on the complexity of these dimensions, you could also set up an iterative macro to go through the joining sequence by dimension.

3 - Regex & Join.yxzp