Join Output Not Showing 8 Records

[alteryxuser_1184]

I imported 2 datasets and joined. Before joining, I implemented "record ID" tool on Precision_Mapping file (2587 rows) to join with "Field Number" on Precision_1 file (526 rows). The join output must only show "8" rows for FileName- ACH_CUSTOM because other files do not exist in Precision_1 file. For Precision_1, I configured Output file name as field as "File Name Only."  Attached "joining workflow" output shows 510 records, however, there should be no more than 8 rows in final output. Attached is the final output in CSV.

[Emmanuel_G]

@alteryxuser_1184 

It will be difficult to answer without having any input data.

Is it possible to share even a dummy sample of datasets ?

[Kamran1991]

for ease of understanding, please share dummy data for both input streams!

[alteryxuser_1184]

attached both files. Precision_1 has 18 files.

[Emmanuel_G]

@alteryxuser_1184 

I did a test and I think it is normal to have so many as rows in the join output anchor.

You're making a join on Field number for all files and master file precision mapping.

However, in the field number on the left side, there are many integers and duplicates of 1,2,3 and many others. So In the join ( all of these numbers will be filtered out seeing that inthe master file precision making, you have a recordID which begins at 1 and have values superior than 2000 ).

To my mind, the field mapping is not correct to have just ACH_CUSTOM in output anchor of join.

The origin of issue is ok for you ?

[alteryxuser_1184]

It is not normal to have more than 8 rows in this final output, "Alteryx mapping" file. "ACH_CUSTOM" is the only mapped file from these 2 datasets. So the expectation is exactly 8 rows.

In the this final output file Alteryx Mapping that is essentially an inner join, the left join (Precision_1) from column A-J has correct output. But when you look at the right join column K-O, then all of these data are mapped incorrectly. For instance, filter column J (FileName) by ACH_CUSTOM, see everything is incorrect from column L to column O. Look at the input data Precision_Mapping file; filter ACH_CUSTOM on column C. Now compare the data on both files. What we want in final output's right join column L-O is exactly what we have in the Precision_Mapping file.

Now on the Precision_Mapping file, filter-out everything and start observing from beginning of the cell. The right join is actually joining from the first row of this file. So on the final output Alteryx Mapping file, on column J if you filter the next file and on and on after ACH_CUSTOM, what Alteryx is doing it is joining row after row to Precision_Mapping file. So from left join if some file has 6 rows, then from the right join it is joining row after row not the file itself. This incorrect join could be due to the usage of Record ID tool on Precision_Mapping file.

[alteryxuser_1184 · Accepted solution]

I solved this problem using second key that is derived from "field label" in left table and "raw column name" in the right table. I removed record ID tool. The first key is "FileName" from left table and "raw table name" from right table.