Inner Join and Concatenate

[#1]

Data is currently being pulled from two locations. For instance, there is an Items table USA and Europe and Orders table USA and Europe.

In order to create one items table and one orders table I must perform concatenations to combine data from both databases.

Afterward I want to perform and inner join on tables Items and Orders, but am not able to due to concatenations.

ItemTable:

Load

ItemID,

'USA' As Loc;

SQL Select *

From Items;

Concatenate(ItemTable)

Load

ItemID,

'EU' As Loc;

SQL Select *

From Items;

Inner Join (ItemTable)

OrderTable:

Load

OrderID,

ItemID;

SQL Select *

From Orders;

Concatenate(OrderTable)

Load

OrderID,

ItemID;

SQL Select *

From Orders;

This code does not seem to work though. Any suggestions would be greatly appreciated.

[#2]

I think that you must do the INNER JOIN after had completely loaded OrderTable. I don't understand why you're loading 2 times the OrderTable in the same way both.

[#3]

You are joining to ItemTable which will add the OrderID field to the ItemTable when you try to concatenate to the OrderTable it will cause an error because there is no OrderTable. If you use KEEP function it will do the join and keep the table or you could use EXISTS function all so. Below is example of keep

OrderTable:
Inner Keep (ItemTable)
Load
OrderID,
ItemID;

SQL Select * From Orders;

[#4]

Would the OrderTable perform all the concatenations before doing the Inner Keep?

[wizardo]

hi

the problem is in the 4th load because

the join is performed before the orders tables get concatenated.

what needs to be done is:

1. concatenate both item tables

2. store the new table to qvd and drop it

3. load and concatenate the 2 orders table

4. load the items qvd and join it with the orders.

of course you can revers the order of tables.

the same could be done without droping the first concatenated table and using it the 2nd time from resident

but you will be surprised that doing it the qvd way, works much faster (there is a thread about it on the forums somewhere)

hope it helps some

Daniel