Combining Data

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:

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

Input

Output

Best Practices

Join

Accepted answers

T_Willins

@quims,

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

Combining Data.png

Combining Data.yxmd

TonyA

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

(Made some edits to the original post for clarity)

Blend Tables.yxmd

All comments

T_Willins

@quims,

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

Combining Data.png

Combining Data.yxmd

TonyA

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.

Comparing Joins.yxmd

TonyA

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

Hi @TonyA

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

quims

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703