full outer join and concatenate

[patricesalem]

Hello

I have two tables : Orders and Invoicing

Both are linked by ID_Order Number.

No problem to link them with a simple load join :

In a section, I have the order table with the following:
[Order_Entries]:
ID_Order,
Customer_Number,
Delivery_Town,
Qty
Sales

In another section, I import invoices and credit notes from an Excel datasource.
Unfortunatelly, the order_entries table does not contain credit notes...so I must concatenate credit_notes information to the merge table Sales & Invoiceing.

The "merge" is done as follow:
[INVOICING&CREDIT]:
LOAD
ID_Order,
Invoice_Type,
Amount
from [lib://invoicing (xxxx)/invoices.xlsx] (ooxml, embedded labels, table is data);

If I leave it as is, ID_Order present in [INVOICING&CREDIT] and missing from [Order_Entries] won't be included in my final data set.

So, I have tried :
[INVOICING&CREDIT]:
LOAD
ID_Order,
ID_Order as ID_Order1,
Invoice_Type,
Amount
from [lib://invoicing (xxxx)/invoices.xlsx] (ooxml, embedded labels, table is data);

concatenate [Order_Entries]:
LOAD
ID_Order,
ID_Order as ID_Order1,
Invoice_Type,
Amount
where not exists (ID_ORDER,ID_ORDER1);

But it does not work..any idea ?

The result would be looking as follow:

Order Entries
Order_ID	Customer Nb	Qty
1	A	5
2	B	10
3	C	15

Invoices and credit notes
Order_ID	Invoice Nb	Value
1	XXX	10
2	YYY	20
3	ZZZ	30
4	WW	40

Final Table
Order_ID	Customer Nb	Qty	Invoice Nb	Value
1	A	5	XXX	10
2	B	10	YYY	20
3	C	15	ZZZ	30
4			WW	40

thanks

[marcus_sommer · Accepted solution]

I think they are there but you don't see the Facture data on top of your table. I suggest to add another extra field [Source] to both loads, like:

'Sales' as [Source] 

and 

'Facture' as [Source]

Now you could differ which ID comes from which table and also use this flag-field to filter the data.

- Marcus

[patricesalem]

@marcus_sommer thanks it did work finally. My issue was mainly due to section access. As there was no sales rep in the "facture", section access was hidding the records...

so thanks !