Concatenate combined with joins

[#1]

Im trying to join two tables(creating the kartesisches produkt between them) and thereafter concatenate the result to other tables being created the same way, see below:

ResGrouping:
LOAD 
           'Försäljningsintäkter' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2');
JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd);
Concatenate (ResGrouping)
LOAD 
           'Direkt material' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2' 
     and (ACCOUNT_GROUP1_CODE='320'
     or ACCOUNT_GROUP2_CODE='257'));
JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd);
Concatenate (ResGrouping)
LOAD 
       'Produktionsomkostnader' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2' and (ACCOUNT_NUMBER like '5*' or ACCOUNT_NUMBER like '6*' or ACCOUNT_GROUP1_CODE='327'));
JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd)
WHERE(COSTCENTER_LEV1_KEY='1' or COSTCENTER_KEY='41200')
etc....

The first join is successfull creating the desired result:

But all the following loads are wrong:

Im guessing it is because the load is done in order of the statement. That is why the first join is ok and then it concatenate before it joins creating the wrong result seen in image 2.

How do I tell it to first join the two tables and thereafter concatenate it to ResGrouping? Or is it something else that im doing wrong?

EDIT: Added another load to the code.

[Gysbert_Wassenaar]

First concatenate the two loads from DimAccounts.qvd and then join that with the data from DimCostcenter.qvd

[#3]

Hi,

Thanks, but that would not generate the correct kartesisches produkt for this example (should have been more specifik in my first post):

Concatenate (ResGrouping)
LOAD 
  'Produktionsomkostnader' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2' and (ACCOUNT_NUMBER like '5*' or ACCOUNT_NUMBER like '6*' or ACCOUNT_GROUP1_CODE='327'));
JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd)
WHERE(COSTCENTER_LEV1_KEY='1' or COSTCENTER_KEY='41200')
;

In the joins i need to be able to filter on both DimCostcenter and on DimAccounts.  Creating the kartesisches produkt for the specifik "Title".

The filters will be different between the different titles.

(Example:

Productioncost is connected to all account numbers like '1*' and costscenter keys starting with '1*'

Financialcosts is is connected to all account numbers like '3*' and costscenter keys starting with '4*')

[Gysbert_Wassenaar · Accepted solution]

In that case you need to create the joined tables first and concatenate them afterwards. You can use resident loads to do this or you can store the joined tables in qvd's and load from the qvd's. That could be faster than the resident loads.

[#5]

OK.

Tried it and it worked, thanks.

ResGrouping:
LOAD 
  'Försäljningsintäkter' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2');
 JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd);
tmpPartResGrouping:
LOAD 
  'Direkt material' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2' and (ACCOUNT_GROUP1_CODE='320'
or ACCOUNT_GROUP2_CODE='257'));
JOIN 
LOAD 
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd);
Concatenate (ResGrouping)
LOAD
          *
RESIDENT tmpPartResGrouping;
DROP TABLE tmpPartResGrouping;
tmpPartResGrouping:
LOAD 
  'Marknadsföringskostnader' as Title,
          ACCOUNT_KEY  as %Konto
   FROM
$(QVD_PATH)DimAccounts.qvd
(qvd)
WHERE(ACCOUNT_TYPE_CODE='X2' and (ACCOUNT_GROUP2_CODE='259'));
JOIN 
LOAD
          COSTCENTER_KEY  as Kostnadsställe
FROM
$(QVD_PATH)DimCostcenter.qvd
(qvd);
Concatenate (ResGrouping)
LOAD
          *
RESIDENT tmpPartResGrouping;
DROP TABLE tmpPartResGrouping;
etc...