Join Issue

[malcorr]

Hello Everyone,

I have 2 sets of information: "Custom" and "Base". What I need is i need to join them and look for every "Row" that is listed in the "Custom" set and bring the corresponding value in the "Base" set, along with the corresponding accounts under each. See below

Custom

Row	Description	Custom Dataspec
76750	A. FOREIGN DIVIDENDS	INPUT
		8207065  1          STATE ADJ FOR SEC 965 INCOME                         W
		6044     -1         FOREIGN CORPORATIONS-OTHER                           E
		6045     -1         FOREIGN CORPS.-SUBPART F(CONTROL                     E
		8ID2406  1          FOREIGN DIVIDENDS (WATER'S EDGE)                     E
85218	TOTAL INCOME UNDER 965(A)	INPUT
		8207065  1          STATE ADJ FOR SEC 965 INCOME                         W
86160	ROYALTY  INCOME	INPUT

Base

Row	Description	Base Dataspec
76750	FOREIGN DIVIDENDS	INPUT
		6043090  -1         FOREIGN CORPS.-100% SEC 245B                         W
		6043090  -1         FOREIGN CORPS.-100% SEC 245B                         E
		6043190  -1         FOR CORPS - 100% SEC 245 SUB TO                      W
		6043190  -1         FOR CORPS - 100% SEC 245 SUB TO                      E
		6043RT1  -1         FRGN CORP-100% SEC 245B-REPORT T                     W
		6043RT1  -1         FRGN CORP-100% SEC 245B-REPORT T                     E
		6042050  -1         FOREIGN CORPORATIONS - 65%                           W
		6042050  -1         FOREIGN CORPORATIONS - 65%                           E
		6042150  -1         FOR CORPS - 65% SUB TO 832                           W
		6042150  -1         FOR CORPS - 65% SUB TO 832                           E
		6042RT2  -1         MORE THAN 20% OWNED CFC-REPORT T                     W
		6042RT2  -1         MORE THAN 20% OWNED CFC-REPORT T                     E
		6042051  -1         FOREIGN CORPORATIONS - 50%                           W
		6042051  -1         FOREIGN CORPORATIONS - 50%                           E
		6042151  -1         FOR CORPS - 50% - SUB TO 832                         W
		6042151  -1         FOR CORPS - 50% - SUB TO 832                         E
		6042RT1  -1         LESS THAN 20% OWNED CFC-REPORT T                     W
		6042RT1  -1         LESS THAN 20% OWNED CFC-REPORT T                     E
		6045     -1         FOREIGN CORPS.-SUBPART F(CONTROL                     W
		6044     -1         FOREIGN CORPORATIONS-OTHER                           W
		6044     -1         FOREIGN CORPORATIONS-OTHER                           E
85218	ADJ TO TCJA ADDITIONS TO FTI	INPUT
		8207065  1          STATE ADJ FOR SEC 965 INCOME                         W
		8207025  1          SEC 163(J) C/F INT EXP STATE ADJ                     W
		8207068  1          ADJ TO TCJA ADDITIONS TO FTI                         W
86160	ROYALTY  INCOME	INPUT
		7304     1          ROYALTY INCOME                                       E
		7315172  -1         ROYALTY INCOME EXCL-E                                W

The end result I need is listing each row and show all the corresponding values under the "Custom Dataspec" and "Base Dataspec". The problem is that, as you can see, sometimes, the "Base Dataspec" contains more values than the "Custom Dataspec" and i Dont get the full list of each. The end result i need is:

Row	Description	Custom Dataspec	Base Dataspec
76750	A. FOREIGN DIVIDENDS	INPUT	INPUT
		8207065  1          STATE ADJ FOR SEC 965 INCOME                         W	6043090  -1         FOREIGN CORPS.-100% SEC 245B                         W
		6044     -1         FOREIGN CORPORATIONS-OTHER                           E	6043090  -1         FOREIGN CORPS.-100% SEC 245B                         E
		6045     -1         FOREIGN CORPS.-SUBPART F(CONTROL                     E	6043190  -1         FOR CORPS - 100% SEC 245 SUB TO                      W
		8ID2406  1          FOREIGN DIVIDENDS (WATER'S EDGE)                     E	6043190  -1         FOR CORPS - 100% SEC 245 SUB TO                      E
			6043RT1  -1         FRGN CORP-100% SEC 245B-REPORT T                     W
			6043RT1  -1         FRGN CORP-100% SEC 245B-REPORT T                     E
			6042050  -1         FOREIGN CORPORATIONS - 65%                           W
			6042050  -1         FOREIGN CORPORATIONS - 65%                           E
			6042150  -1         FOR CORPS - 65% SUB TO 832                           W
			6042150  -1         FOR CORPS - 65% SUB TO 832                           E
			6042RT2  -1         MORE THAN 20% OWNED CFC-REPORT T                     W
			6042RT2  -1         MORE THAN 20% OWNED CFC-REPORT T                     E
			6042051  -1         FOREIGN CORPORATIONS - 50%                           W
			6042051  -1         FOREIGN CORPORATIONS - 50%                           E
			6042151  -1         FOR CORPS - 50% - SUB TO 832                         W
			6042151  -1         FOR CORPS - 50% - SUB TO 832                         E
			6042RT1  -1         LESS THAN 20% OWNED CFC-REPORT T                     W
			6042RT1  -1         LESS THAN 20% OWNED CFC-REPORT T                     E
			6045     -1         FOREIGN CORPS.-SUBPART F(CONTROL                     W
			6044     -1         FOREIGN CORPORATIONS-OTHER                           W
			6044     -1         FOREIGN CORPORATIONS-OTHER                           E
85218	TOTAL INCOME UNDER 965(A)	INPUT	INPUT
		8207065  1          STATE ADJ FOR SEC 965 INCOME                         W	8207065  1          STATE ADJ FOR SEC 965 INCOME                         W
			8207025  1          SEC 163(J) C/F INT EXP STATE ADJ                     W
			8207068  1          ADJ TO TCJA ADDITIONS TO FTI                         W
86160	ROYALTY  INCOME	INPUT	INPUT
			7304     1          ROYALTY INCOME                                       E
			7315172  -1         ROYALTY INCOME EXCL-E                                W

Please note that these are part of a larger list 

Any ideas?

[john_miller9]

@malcorr 

Here's one solution that leverages the multi-row formula and the tile tools.  Some extra steps at the end to remove the duplicate [Row] field, but if that isn't required it's a much shorter workflow.  Let me know if this helps.

jm

[christine_assaad]

Hi @malcorr 

I built a batch macro to perform this operation. It could be streamlined further. Hope it helps

[malcorr]

Hello, 

Thanks for your reply, I'm not able to open the second file since it says I dont have the latest version. 

I can open the first one, but given the fact that the inputs are saved in your desktop i cannot run it. Could you please send them seprately? 

Thanks!

[john_miller9]

@malcorr- I've added the excel data input file now.  See if you can access

[malcorr]

Thank You,

Te excel Worksheet you added is the first input in your workflow? In that case, what do i do with the second function, the Dynamic Input??

thanks!

[malcorr]

Thanks!

How do you configure the Macro??

[john_miller9]

@malcorr 

The configuration for the dynamic input is below.  In the Input Data Source Template field, you'll want to point to the excel file and choose one of the sheets.  Also be sure to select "Full Path" in the Output File Name as Field section of the Options. 

Then you would copy one of the values into the Filter tool so that the workflow splits into the 2 datasources that you're trying to join.

[malcorr]

I tried and configured exactly as indicated, but seems im not getting the same result as you 

[malcorr]

Seems like the filter is not working properly. I have this configuration. On the "False" Side of the filter, Im getting the "Base" numbers, but I'm not getting anything on the "True" Side of the filter