Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you so much Erich!! That solved the problem!!