Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Mutiple Join Tool Null

Chirag_Gandhi07
8 - Asteroid

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

5 REPLIES 5
ivoller
12 - Quasar

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
11[Null][Null][Null]
[Null][Null]22[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
17 - Castor
17 - Castor

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])

Chirag_Gandhi07
8 - Asteroid

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.

zachwatts
5 - Atom

I'm having a similar problem.  I have a join multiple with 6 inputs.  None of these six inputs have null values in them.  They all have 45 actual valid values.  Coming out of the join multiple I have 46 records.  One of these has null in every field.  The only thing I can do is use a filter AFTER the join multiple to filter out the random null record.

slatz
5 - Atom

The only way that I have found to use the join multiple tool in this case is the second path within my attached example.

Labels