Merging 2 tables with multiple conditions

Hi,

I am kind of stuck with a scenario for which I'm not able to resolve. I have 2 input tables with 2 common columns. The output must be 1 table with a merge of results from both tables. Each Sub-LOB should show individual totals and the compliance % per Sub-LOB must be displayed along with the other columns causing the non-compliance numbers and relevant control managers. Its pretty complicated, any help with this would be much appreciated. Thanks !

Input 1.xlsx

Input 2.xlsx

Result table.xlsx

Reporting

Transformation

Join

Preparation

Accepted answers

Qiu

@NeilSwithin
I think I understand the situation here.
If we do the joint right away, it will result in a 1 Vs many situation, which is not we need.

I am using sample tool as below and hope this is what you need.

0328-NeilSwithin.yxzp

usmanbashir

@NeilSwithin - I think I was able to get it. I did see a couple #'s not matching with your result table, but makes sense when referring to Inputs 1/2. It's a little messy, but hope this is what you were looking for!

table_join_cleanup_v2.yxmd

All comments

usmanbashir

@NeilSwithin - I see some oddities in your Input 2 within the [In Business CDO] field. Specificlly #REF!, TC, and 'align to...'. Those don't appear in your result table. So I'm a little confused on your data set.

Qiu

@NeilSwithin
I think I understand the situation here.
If we do the joint right away, it will result in a 1 Vs many situation, which is not we need.

I am using sample tool as below and hope this is what you need.

0328-NeilSwithin.yxzp

NeilSwithin

@usmanbashir- Apologies, I have corrected the inputs. Please refer the attachments.

Thank you for your response. Solution was almost good, but was not matching the result table. I tried to join the left and right output of the join tool into the union tool which got me closer to the result. But needs further sorting and totaling of columns. Appreciate if you could help.

usmanbashir

table_join_cleanup_v2.yxmd

NeilSwithin

Thank you so much, this works !

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703