Join tool not working properly - not joining data

[nataliad18]

Hi everyone,

Have a below puzzle to solve and I am struggling.

Using join tool and joining by 1 field (ID). Left input- ID is a double and right input - ID is a double. 

Upon joining the tools, it does not match them. There should be over 1611 fields matched, it only finds 3. The numbers are definitely same. 

Does anyone have any idea how to solve this? See below (first left input, then right, then join tool config).

[grazitti_sapna · Accepted solution]

@nataliad18, can you provide some sample data or screenshots of the numbers that are not matching?

Also, did you check if there is some kind of leading or trailing space in the ids in any of the tables?

You can try using data cleansing tool on both the sources then select the id column and then check leading and trailing whitespaces.

Thanks!

[DawnDuong]

hi @nataliad18 

As a best practice, you should avoid joining by double or float data types.

Instead, when using Join tool, it's better to join by String, Integer, Boolean or even fixed decimal.

I see here that the fields are meant to be ID. So you should change the data to String types (check the appropriate length and whether there are special characters in which case maybe VWString may be more appropriate).

Dawn.

[Qiu]

@nataliad18 
I think joing data type of "Double" is not possible I think.

[nataliad18]

Thanks! I just changed both to String (and tried WString also), but it still gives me the same output

[DawnDuong]

@nataliad18 

Can you share the inputs and expected outputs? 

One possible reason is because the ID does not exactly match e.g. have trailing / leading spaces, upper/lower case, extra spaces....

Dawn.