Issue with outer join having blank fields

mohamed

Forums

Hi,
I am facing an issue with full outer joining of 2 files.I am joining with 2 matching columns.  Blank fields in both files are considered as match and produced a combined row in the joined file.
Attached excel is having 4 sheets.
First 2 are primary and secondary
3rd - Actual output file after joining
4th- Expected output
Joining fields
PON (P), GRNPO (S) ASCENDING
POLN (P), GRNPOL (S) ASCENDING
Joining type: All records in both files (Full outer join)
PON and POLN in row number 5 of primary file is blank.
Ideally this should be added as a separate row in join file in which all fields from the secondary file should be blank. But we are getting values from secondary file also, as given below.
This is happening because, joining is done with blank values as well.
Is there any way to produce the output similar to the expected output
Thanks,
Shafeer

Brian Element Fri, 08/12/2016 - 11:59

Hi Shafeer,

To get the outcome that you wish you will have to do this in multiple steps.  The first thing you need to do is split your primary and secondary files each into two files, one that does not contain blanks for your key and one that does contain the blanks.  You would then do the same join on the files that contain the non blanks.  This would give you records 4 to 7 of your expected result. 

The next step is to perform an append on the join file and the two files that contain the blank.  So this will add records 1 to 3 from your expected to records 4 to 7 from your join.

Hopefully that makes sense.

Brian

mohamed Fri, 08/19/2016 - 05:53

Thanks Brian for your suggestion.
It is working with below steps.
Steps
1. Extract records WITHOUT blanks from both files into separate files
2. Extract records WITH blanks from both files into separate files
3. Join extracted fles from step1
4. Append records of file1 from step2 with join file obtained in step3.
5. Append  records of second file from step2 with first file. (No records from first file are selected. This is to create a file which can be appended with file from step4).
6. Append file from step 5 into file from step 4