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

Combining Data

quims
8 - Asteroid

Hi Alteryx Community,

 

I would like to ask how can i show a specific line of data which is not present in the other source raw data.

 

Sample:

I have Data A and Data B, both have identical unique data which can be combined but what if there is one data is not present in the other raw data? What function will i use? Currently, I'm using Join Tool but it doesn't work.

 

 My Data is as below:

quims_0-1580430857066.png

 When i use Join Tool, it will combined based on the criteria i have above except SO Ln# 03 which is not present in Data B. How can i troubleshoot this issue to make this missing line still showing in my final output file.

 

Appreciate your help.

 

quims

6 REPLIES 6
T_Willins
14 - Magnetar
14 - Magnetar

@quims,

 

You can use a Union tool after the Join tool to bring all the data back together:

 

Combining Data.png

TonyA
Alteryx Alumni (Retired)

There are a number of ways to do this depending on the result you need. If you really need to know the source data sets for all the data in the result set, then I would use a Join Multiple instead of a regular join.

 

All the join tools will create distinct columns for the data from each source. For the simple join tool, by default, the columns from the left input will keep their original names and the columns from the right input will have a prefix of "Right_" appended to the name. This renaming only applies to the J output. The L and R outputs (for unmatched rows) keep their original names. If it's important that you know which rows came from which data set, this can cause problems. 

I attached an example with three different join configurations. The first two use the simple join tool. I put the B data set on the left input because it's the one that has less rows.

 

In the top example, the B column labels have no prefix and the A labels have a prefix of "A". Note that the unmatched row in A is passed through on the R output without being renamed, so in the joined data set, the data in that row is in the columns with the B data instead of the A data where they belong.

 

In the middle example, both the B and A columns have prefixes. In this output the unmatched row will go to a third set of columns with the original column names. Note that if there are unmatched rows in both data sets, all of those rows will be lumped into the third set of columns regardless of which data set they came from.

 

The last example uses a Join Multiple tool. This tool behaves more like a database outer join. All rows come out on one output and all columns are labeled according to their data set. Unmatched rows from each data set will go to the appropriate columns with nulls in the columns that correspond to the other data set.

 

I know the description is somewhat complicated, but look at the outputs in the workflow and I think you'll see what I mean.

 

TonyA
Alteryx Alumni (Retired)

One other thing. I know you said you were using a join, but exactly what output are you looking for? Is the image you have in your posting only to show us the data sets or is that actually the output you need?

 

If you want to create that output from two data sets, you would use a Union tool. You would also need to include header lines on each data set with the names. Let us know if that's what you need and one of us can build an example for you pretty quickly.

quims
8 - Asteroid

Hi @TonyA 

 

Thank you for your explanation and i apply it already but something i really want the results to be like this.

 

quims_0-1580440615295.png

 

quims

TonyA
Alteryx Alumni (Retired)

Then my solution looks a lot like @T_Willins solution but the configuration of the join is different. Union the left and right outputs and the join results from the left data set keeping the original names. Depending on the data, the union should combine the rows in order (if they aren't in order, you can add a sort after the union).

2020-01-30_21-58-12.png2020-01-30_22-00-26.png

(Made some edits to the original post for clarity)

 

 

quims
8 - Asteroid

Thank you for the help @TonyA and @T_Willins

 

I'm good to go now.

 

Glad to have this community - very helpful!

 

quims

Labels