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]

Can you post your workflow? 

The filter just acts as a way of splitting the files back into two workstreams after you apply the multi-row formula, so you can add the tile tool and join the datasets.  You could similarly add 2 data inputs (one for each sheet) to the canvas and apply the multi-row formula in each workstream.

[malcorr]

Here it goes, 

I tried applying something similar to yours. 

If you see the final result, I get pretty close to what i need, but look for example at row 76750, it's not bringing the full list of values under the "Base Dataspec" column 

Thanks!

[malcorr]

Sorry, 

Here is the exported Workflow.

Regards

[john_miller9]

@malcorr,

A few things:

• It looks like there is no row 76,750 in the packaged workflow you provided.  Looks like your output only has 135 records
• In my workflow, i used a Join Multiple tool whereas you are using a join and union tool, but you're missing one side of the output.

If you can add some more context around what your desired output should be (and/or how the workflow I posted isn't addressing that need), that would help to provide the solution you need. 

[malcorr]

Hello John, 

Sorry if I Didn't express myself correctly in my previous messages. These are my very first attempts with Alteryx

Attached you'll find:

• My workflow - ("ID CUSTOM VS BASE" File)
• the 2 inputs - ("ID BASE CALC" And "ID CUSTOM CALC" Files)
• The Output I get with my current Workflow And the desired one ("ID CUSTOM VS BASE ALTERYX CURRENT OUTPUT" File and "ID CUSTOM VS BASE ALTERYX DESIRED OUTPUT" File)

What I'm trying to do, if you see both output files, is to list every [Row] in the "ID CUSTOM CALC" file and compare the corresponding value of each of those in the "ID BASE CALC" file. The output is for comparison purposes. 

in the "DESIRED OUTPUT" File i highlighted in red the values that my current workflow is not able to pull in in the comparison, and that's what i need to fix. 

Bassically, whenever the "Base dataspec" column has more values for a [Row] than in the "Custom Dataspec" column, those extra values don get incorporated in the final output file.

I hope this clarifies. 

Thanks for the help.

Regards,

[john_miller9 · Accepted solution]

Thanks for the clarification @malcorr 

This workflow should get you there, or at least very close to what you're looking for.

[malcorr]

Thank you so much!