Categories
Isolate Data Flow Records that did NOT Join
The Join step within Data Flows should have the ability to show what records did NOT join.
For example:
A table that contains records from the L input that did NOT join to records from the R input.
A table that contains records from the R input that did NOT join to records from the L input.
Users often have requirements to isolate and modify certain data that did not join.
Comments
-
You should be able to do this using 'All rows' on both input and apply a filter to get either scenarios. So if you want to get records from L that do not have corresponding records in R, you need to apply a filter on one of the L's key columns where the value is NULL. The same logic can be applied to get records from R.
0 -
@Matt_B did the above suggestion address your request? if not, please add more details.
0 -
@Syaifuddin Ismail-Oracle @Farivar Javanbakhti-Oracle I agree this works from functional prototype perspective but is there any known issue that causes (or best practice(s) to circumvent) false negative returns? For instance: I have a retrieved/lookup value from R appearing as a NULL even though the joined account number is confirmed to be present in both L and R.
0 -
@Matt_B
I think I may see where you're coming from:
In a way, what you're wanting the Join function to do is to help you identify where the data may need to be cleansed so you can make the fixes and reload it.
For example, Left Table and Right Table may both have account #12345, but perhaps the Right table has a trailing space after the account number, so it's not reflected in the join and therefore appears as NULL. You're looking for a way to identify all of the account numbers that weren't joined (or, as you phrased it, isolate and modify them).
Is that kind of what you're getting at?0
