Mutiple Join Tool Null

Hi,

I am attempting to join four excel files together using the multiple join tool and asset number as the specific join field. There are a total of 67,198 records but I am not sure why the first 25,000 contain null values and there are null values in between as well. All four asset numbers from each file have a data type of double. I was assuming that it would output the asset numbers without any null rows. Am I doing something incorrect. Any help would be much appreciated.

Thank you,

Chirag Gandhi

AssetNumbermultiplejoin.PNG

Join

Accepted answers

ivoller

Hi @Chirag_Gandhi07,

You would expect to see Null values for any Asset Number that didn't match to an asset number in a different input.

For example:

Input 1 - Asset number 1

Input 2 - Asset number 1

Input 3 - Asset number 2

Input 4 - Asset number 2

Input 5 - Asset number 3

When joined on Asset Number would result in something like

Asset Number = AN

AN	#2 AN	#3 AN	#4 AN	#5 AN
1	1	[Null]	[Null]	[Null]
[Null]	[Null]	2	2	[Null]
[Null]	[Null]	[Null]	[Null]	3

If you can share sample data and advise on what results you were expecting, we can give more help.

CharlieS

Excel likes to consider null rows/records as valid inputs. Put a browse after each of the Input tools and scroll to the bottom to see if they are there. They appear first in your join result because the join field is sorted when joined so the null values will be sorted to appear first.

To resolve this, put a Filter tool after each Input to remove the records will null Asset Numbers using the following formula:

!isnull([Asset Number])

All comments

ivoller

Hi @Chirag_Gandhi07,

You would expect to see Null values for any Asset Number that didn't match to an asset number in a different input.

For example:

Input 1 - Asset number 1

Input 2 - Asset number 1

Input 3 - Asset number 2

Input 4 - Asset number 2

Input 5 - Asset number 3

When joined on Asset Number would result in something like

Asset Number = AN

AN	#2 AN	#3 AN	#4 AN	#5 AN
1	1	[Null]	[Null]	[Null]
[Null]	[Null]	2	2	[Null]
[Null]	[Null]	[Null]	[Null]	3

If you can share sample data and advise on what results you were expecting, we can give more help.

CharlieS

To resolve this, put a Filter tool after each Input to remove the records will null Asset Numbers using the following formula:

!isnull([Asset Number])

Chirag_Gandhi07

The excel files have no null values for asset number but I was unaware that the multiple join tool shows nulls for records that don't match. I guess I am stuck using the union tool, but the problem is that there will be 105,658 asset numbers. Maybe I could use join tools and split the input data files up and then connect the streams to the other input data files.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703