Scenario #1 is probably working correctly. This might happen if the field(s) you are joining on occur more than once in the second file.
For example, say you have this data in file 1:
And in file 2 you have:
If you left join on the number field, you'll get three lines back, even though there are only two in file 1:
1 Green Yellow
1 Green Purple
2 Blue Red
Does that make sense? The data blending (scenario 2) works totally differently, in that it can only return one result from file 2 regardless of how many lines there are. It forces a situation that doesn't reflect the truth in the underlying data, and you'll see this anywhere it returns * instead of the value because it finds more than one match.
Thanks so much! That was exactly the issue I was having.
I had thought I cleaned up my file #2, but when I checked again I found a record that was occuring more than once. Which was causing the extra records to appear when I was doing a left outer join in my Scenario #1.
Once I gotten that cleaned up, Scenario #1 worked correctly, it returned the correct number of records it should have.
Thanks for the insight to Scenario #2 and how it works too! I didn't realize that the data blending works a little differently between the two scenarios.
I am facing a similar as mentioned by Andy. I did create a relation by following Scenario#2 but still i am not getting the accurate number. I am trying to calculate attrition rate and when i am joining, it is only taking the counts from the groups where employees have termed. It should take the total employee count and not limit the counts to the group where the employees resigned. Can anyone please guide me.