Joining two results back together

[cowannbell]

I have a table with a filter for tax ID type.  So I filter for E as the type.  I then want to see all of the records with E and all of the records with S as a type that match on Tax ID.  So the just is that I want to see rows where the TAX is listed as E and S.  I don't want the left and right on the same row, I want all left and all right on their own rows because I need to do more filtering down the line.

I can't seem to get this to work right using the Join tool.

What tool do I need to accomplish this?

Thanks,

[seven]

Hi @cowannbell 

have you thought about using the Union tool? It will stack the rows for you. Simply connect the desired inputs from the Join tool into the Union and experiment until you get the rows you want.

[seven]

Hi @cowannbell 

here is a great guide with visuals for you:

https://community.alteryx.com/t5/Alteryx-Designer/Tool-Mastery-Union/ta-p/32872

[cowannbell]

I did try the union tool but it didn't seem to work for what I needed.  I think the issue is that I have one input file. Then I use a filter tool to separate values based on that one field.  So I have true and False.  What I need to do now is join true and false back together but only where their TAX ID is the same. So give all records from true and all records from false where TAX ID is on both true and false.  The join tool does that but adding it all into one record(row) and not keeping them seperate.

[DannyS]

@cowannbell 

Would you be able to post some sample data? And can you describe your final output a bit more?

One immediate suggest i have would be to add a record ID as well as a source column (e.g. add a Formula tool and label the True side records as "True" and False side as "False) to both the T stream and F stream. This will give you that clear visual post-join as to where the matching TAX IDs are coming from and you can even join those records back to either the True and False anchors from you started with.

Curious though, if you do not want your final outcome to be the match record set with full rows that matched from the True and False side, what are you looking for?

[cowannbell]

I have more filtering to do down the line. Let me see if I can explain this in full.

I am pulling data in from an IBM odbc.  I need to find records in this source where the same TAX number is listed as both E and S type.  I then have to separate those records out by plan type.  So that TAX ID and PIN are either only in Plan 1 only  or In Plan 1 and plan 2.

The first filter is based only on TAX but the second is based on TAX and PIN which is personal ID number.

There are several other fields being pulled in also like name and address and such but I don't need to filter on those, only include them in the output.

[seven]

Hi,

please include sample input data and sample output data. You mention that you have a few filters already. What is in those filters?

[cowannbell]

It's not about the data.  I just want to know how to pull records that have a field called tax id and a field called tax id type.  So pull all records where tax id number is both s and e.

I want all the records and I don't want them listed on the same row, as right and left just all records on their own row.

This would be like running an MS Access select query that says show all the records from table one and all the records from table 2 where tax id number is the same.

I hope this helps.

[seven]

Hi @cowannbell 

add a RecordID tool to the stream. Join the records to themselves to find those that have E and S. I know you don't want them side by side in the Join. The point of the join here is just to figure out those records that have both E and S. That list can be grouped using a Summarize tool. That is now a distinct list of RecordIDs that correspond to your constraints. Use that list to join back to the original stream and now you have your rows. It is pictured below.

[cowannbell · Accepted solution]

You were close and got me to thinking correctly.

Here is what I had to do.

I added the record ID

I then did the select for E on Tax ID format to get E on true and S on False

I then did the join on TAX ID, this gave me the records were TAXID was listed as Both E and S type

I then used the arrange tool to combine the left record ID and the Right record ID into a new field called combined record ID

I then did the join tool to pull in the full records from the original file matching Record ID to the new Combined Record ID.  This gives me all of the records.

I was not familiar with the arrange tool.  This tool is exactly what I needed.

Thank you so much for the direction.  It got me to thinking in the right direction.