Certain SKUs are not matching in Join tool. What else can I do to troubleshoot?

[Ssmit114]

I have tried the following with no success: 

Adding cleanse tool to both data streams to remove blanks, whitespace, tabs, etc.

Adding a select tool to ensure both data types are set to v_string

Adding a formula tool to trim spaces not caught by cleanse tool

Adding a formula tool to manually detect the unmatched SKUs and match them

Adding a formula tool to ensure all characters are the same case. 

I suspect the issue has something to do with the fact the product SKUs are letters, numbers, and characters (. and -). However, there is nothing consistent in the unmatched SKUs to point to a specific problem. What else can I try to troubleshoot this issue?

[alexnajm]

Can you give us some examples of records that are not joining from both sides?

[Ssmit114]

7000-JRW4

TG17-525R

03.312.740

Most of the SKUs in the data set are set up like ##.###.### and they match fine. Even ones that are ##.###.###LLL are working. It seems random the ones that are not. 

[alexnajm]

Sorry let me clarify my ask - can you give examples of records on one side that you were expecting to match examples of records on the other side?

[Ssmit114]

Maybe I am misunderstanding as well. Hopefully this answers your question:

The SKUs feeding into R join should match with SKUs in L join. The desired/expected result is adding a column from R join which populates value "Y" for matches and null() for values not in the R data set.

So, the few examples I commented above should be matching and populating the "Y" in the added column. However, they result in the null() value. 

[alexnajm · Accepted solution]

Right, which means one of two things:

- Those values exist in one dataset but not the other, so there is no possibility of a match... OR

- you think they exist on both sides but some small difference is causing them to not join.

I suspect your issue is the latter, so we need examples to be able to tell you what can be done to get them matching. Data, a workflow, screenshots, or anything else to help us diagnose the issue would be great - otherwise we won't be able to help further

[Ssmit114]

Unfortunately I cannot share the workflow due to data confidentiality. I have an excel file that was manipulated manually with an Xlookup/vlookup. So Excel recognizes them as matches, but Alteryx does not. As stated above, I have tried all the cleanse/data type/case sensitive options. Do you have any other suggestions for trouble shooting? 

[alexnajm]

Without knowing more about the values that are "matching" up, we cannot help further - the things that you did are good, but anything further will need concrete examples

[apathetichell · Accepted solution]

@Ssmit114 --- they are not matching because the values are different. That's it. It's simple. Is it a space? Is it an extra character? who knows. That's your job. I've explained to you why they are not matching though.

[EKasminsky]

Take both of the mismatched lists (L and R in join) and maybe check to see if the character length is difference.

Use LEN([StringField]) to see how many characters are in the field. 

Other than that, it would really really help if you gave us data. I cannot imagine that SKUs are so locked down confidentiality wise.