Data is out of sync for 14 Lines when I run a Join Workflow

[Karl_Spratt]

Hi Community,

I'm joining a shipment file with a frame file to find out the frame sizes were shipping, I've had to create a unique key in both files by concatenating the Item / Description / Description in both files.   I'm loading in the exact same items into both files 14116 records in both. 

But when  I run the Join WF to add the Frame size 14 records are misaligned.

As I'm going to add to this file going forward I'd like to understand the issue and fix it in Alteryx if I can.

I'm attaching the 3 excel files and the WF, the SIM_Monthly_Shipments With Frame.xlsx  is the file with the 14 lines misaligned, and is the output of the JOIN WF. 

1. SIM_Monthly_Shipments.xlsx is the shipment Data 

2. Frame_Matrix.xlsx is the fil with the frame sizes.

Also adding the JOIN WF.  Can someone please advise why I'm getting the misalignment.

TIA,

Karl.  

[Christina_H · Accepted solution]

The problem is due to the way your join and union are set up.  The join is renaming columns from the right input with the Right_ prefix, but then the union can't merge the unjoined records to those same columns because the names don't match.

All you need to do is clear the renames from the join tool and it should work correctly.

[Karl_Spratt]

Thanks Christina that worked. before I accept as a Solution can I ask another Question please when I ran the data the 14 "blanks" are due to Material Description having a different letter case in some of the values. Is there a way dynamically make all the Case say CAPITAL  in the WF where I'm doing the concatenation?

See attached, as you can see the 3 blank values are due to the description of RSe55i-A10 V4 as my CONX is on the capital RSE55I-A10 V4 if I add the lower to my lookup I'm getting 42K records in the output not the 14K I should get. 

Can you advise a solution please.

Regards,

Karl. 

CONX	Material	Description	Material Description	Frame/Product
A349057018Srews Oil Lub.Frame 4 Gear VSDRSE55I-A10 V4	A349057018	Srews Oil Lub.Frame 4 Gear VSD	RSE55I-A10 V4	Frame 4

[DawnDuong]

hi @Karl_Spratt 

The 14 errors (ie. those that are output via the R output of the Join Tool) happen because they are not exact matches to the Frame Matrix. As shown below and in the attached workflows, there are 8 unique values of CONX that cannot be matched. I have not reviewed all of them in details, but the first 5 show that the mismatches arise because of different character case.

Another issue that I think you will need to look into is that CONX does not uniquely identify each record. For instances, there are only 8 unique CONX for the 14 unmatched records from the SIM monthly file.

At the minimum, the Frame file needs to have a unique identifier that is non duplicate, otherwise you run the risk of having many-to-many matching which will produces non-meaningful results.

Hope this helps,

Dawn.

[Karl_Spratt]

Hi Dawn, Thanks 

I changed the join to make it join by the Item / Description / Description 1 , and this seem to work after I  changed the look up file to have the 2 case vales in the lookup.  I didn't get the 400K records but the correct 14K records. 

Thank you for your help here.

Cheers,

Karl. 

[atcodedog05]

Wow that's a great catch @DawnDuong 😀👍 I was not able to find the difference/cause and had to give up 😅

Amazing and was a great learning thank you 🙂

[DawnDuong]

Glad to help @Karl_Spratt  

Cheers,
Dawn.