I have some issues getting my data joined and displayed correctly. I have attached a sample document.
This is what I have done so far (Steps are marked in the script):
1. Load the initial datasource 2. join the secondary datasource to get the Ext_CaseNumber 3. Drop the CaseNumber from initial data, so each case only has an Ext_Number 4/5/6. Load and Join more CaseNumbers to the initial datasource, but as I use Ext_Number as the key I only get data joined on CaseNames that have a Ext_Number.This way, I join CaseNumbers that don't exist in the initial datasource. 7. Join the CaseNumbers from the initial datasource back, so I'll have the CaseNumbers without a Ext_CaseNumber again. 8. Load the data into a new table and remove CaseNames without a caseNumber 9. Join the CaseDates for those CaseNames that don't have an Ext_CaseNumber
I want the field CaseNumber to contain every casenumber from the initial datasource and the casenumbers from the secondary datasource that have an Ext_CaseNumber that matches a CaseNumber from the first datasource. The problem is cases that don't have an Ext_CaseNumber (CaseName C in the sample). C should have a CaseNumber and CaseDate associated with it.
complicated procedure. But of course sometimes it must be complicated ....
Try to do one thing, because it will be easiest for you. I'am sure, you know exactly what result you expect after each of your steps. So, please comment your script after 2 step, reload and check, then uncoment 3'rd step reload and check, then do the same with 4'th step and so on. Try to find step which doesn't work as you expect.