Adding Rows to Data for Items Not Joined

I have two files from different datasets out of the same system that I am trying to create a workflow that calculates the difference between the two (dataset 1 = "Provision", dataset 2 = "return"). When I run a join, I discovered that not all items were matched between the two (ex. I have codes in provision that don't exist in return and vice versa). I would like for all codes to be present in each dataset even if it is zero for all records so I can show a difference (ex. code 1 with value of 10 in provision compared to the same code with value 0 in return because the code doesn't exist in return getting me a difference of 10 in a new column). How can I get items that weren't joined to be added into the datasets they are missing from as zero values so they get captured in my difference column?

Transformation

Join

Accepted answers

Loic

@denvic29

It looks to me that you are trying to do a Full Outer joins (all matched and unmatched rows are in the results).

Shameless self-promotion: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Left-Right-and-Full-Outer-Joins-are-easy/ta-p/424651

You can use the "Multiple Join" tool to do a quick full join.

All comments

estherb47

Hi @denvic29

You could use a formula tool after the records that didn't join, to change the values to zero. Then Union those records back with the data that did join.

Let me know if that helps.

Cheers!

Esther

denvic29

For items that didn't join, I want them to be zero in the dataset they don't currently appear in. They have a value in one dataset and in the other dataset that it doesn't currently exist in I want to write a row that has the code name with zero records. What would a formula look like to add a row into the other dataset missing a code? Once I get the row added where it needs to be, I want to write a formaula comparing the code in both datasets, one will just happen to be zero.

Ex. Code P01 (rows) in dataset provision for state CA (columns) is value of 10 and code P01 doesn't exist in dataset return and shows as an unjoined record. I want to write a row into dataset return showing a new row for code P01 state CA value 0. Then, I want to write a formula showing provision value 10 less return value 0 getting to a new column for "difference", in this example showing 10.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703