Adding Rows to Data for Items Not Joined

[denvic29]

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?

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

[Loic · Accepted solution]

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

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

[estherb47]

So let's say it's the R output from the join that doesn't have the matching data. I'd stream a formula tool, that first writes the value (10 in your example) into a new column, and then replace the Value column with the zero. It would look like this:

Adding columns here, not rows. When you union this back to the data that joined, you'll also have these records, with the value of zero in the Value column, and you maintain the value that was previously there with the OldValue column.

Does that make sense?