problem with join

[#1]

Hi Guys,

I'm having some problems with joining 2 tables.

I have one table with the folllowing data:

Table One:

OrderID, (Start with ULM.... and with JIL...)

Weight,

ProductType= 'Desktop'

Field A,

Field B,

Field C,

Field D,

Field E,

From

CustomerOrders.xls

Table Two:

Purge(OrderID,'-') as OrderID, (Orders only with ULM)

Weight

From,

Select OrderID, Weight

     Where left(OrderID,3)= 'ULM';

The second table does not contain orders that start with JIL, so therefore I filtered it to show only orders that start with 'ULM'

I want to joins these to tables. However if I do a left join (table 1), I get the weights only for the producttype 'Desktop', if I do a right join (table 2) I would only get the weights for Server and the OrderID without the all the other fields. I can't do an inner join, because that would excluse orders that start with JIL.

If I do an outer join, I would get the following results

Which makes sences ,:

OrderID	Weight	ProductType	Field A	Field B	Field C	Field D	Field E
ULM1234567	30 KG	-	-	-	-	-	-
ULM1234567	-	Server	some value	some value	some value	some value	some value
JIL12547889	10 KG	Desktop	some value	some value	some value	some value	some value
JIL12547889	9 KG	Desktop	some value	some value	some value	some value	some value
ULM1234565	35 KG	-	-	-	-	-	-
ULM1234565	-	Server	some value-	some value	some value	some value	some value

Is there a way I can create the below outcome:

OrderID	Weight	ProductType	Field A	Field B	Field C	Field D	Field E
ULM1234567	30 KG	Server	some value	some value	some value	some value	some value
ULM1234565	35 KG	Server	some value	some value	some value	some value	some value
JIL12547889	10 KG	Desktop	some value	some value	some value	some value	some value

I've tried estabilsing a relationship between the OrderID from table 1 and from table 2, but that didn't solve anything.

Does any know how to solve this in the load script?

Regards,

Carter

[erichshiino · Accepted solution]

Hi,

You can create temporary fields Weight1 and Weight2 and then, on an additional step, select one of the values as a final Weight field.

The script would be more or less like this:

Table1:

Load

OrderID, (Start with ULM.... and with JIL...)

  Weight as Weight1,

ProductType= 'Desktop'

Field A,

Field B,

Field C,

Field D,

Field E,

   From

CustomerOrders.xls;

left join(  Table1)

Load

Purge(OrderID,'-') as OrderID, //(Orders only with ULM)

Weight as Weight2

;

   Select OrderID, Weight

from ....

     Where left(OrderID,3)= 'ULM';

FinalTable:

Load *,

if( isnull(Weight1), Weight2, Weight1) as Weight

resident Table1;

drop table Table1;

drop fields  Weight1, Weight2;

(it may need to correct some syntax mistakes here)

Hope this helps

Erich

[#3]

Thank you so much Erich!! That solved the problem!!