join problem

[nicolai_moller]

Hi

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.

Anyone?

[#2]

Nicolai,

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.

regards

Darek

[nicolai_moller]

It's the last step that is giving me trouble. I want to join the date to CaseNumber C, by doing a left join, but nothing changes.

[#4]

Nicolai,

the problem is, that you join CaseNumber and CaseDate to table where botf fields exists. Your row with casenumber = 0003 has casedate=null, this is why it doesn't meet join criteria.

You may try this script:

Datasource1: //Step1

load * inline

[

CaseName,CaseNumber

A,0001

B,0002

C,0003

];

Datasouce2: //Step2

left join (Datasource1)

load * inline

[

CaseNumber,Ext_CaseNumber

0001,0001

0002,0002

0004,0004

0005,0005

0006,0002

0007,0002

];

drop field CaseNumber; //Step3

Date_tmp: //Step4

load * inline

[

CaseNumber,Ext_CaseNumber,CaseDate

0001,0001,01-01-2001

0002,0002,02-02-2002

0004,0004,05-05-2005

0005,0005,06-06-2006

0006,0002,02-02-2002

0007,0002,07-07-2007

];            

Concatenate (Date_tmp) //Step5

load * inline

[

CaseNumber,CaseDate

0003,03-03-2003

];

left join (Datasource1) //Step6

load

*,

1 as A

Resident Date_tmp;

drop table Date_tmp;

drop field A;

outer join (Datasource1) //Step7

load * inline

[

CaseName,CaseNumber

A,0001

B,0002

C,0003

];

New_Table: //Step8

load

*,

1 as DEL

resident Datasource1

where exists(CaseNumber);

drop table Datasource1;

NoConcatenate

newTab1:

load * Resident New_Table where not Exists(CaseDate);

drop Field CaseDate from newTab1;

join (newTab1) //Step9

load * inline

[

CaseNumber,CaseDate

0003,03-03-2003

];

load * Resident New_Table where Exists(CaseDate);

drop table New_Table;

regards

Darek