This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.
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.