Solved: full outer join and concatenate - Qlik Community

patricesalem · ‎2020-09-01

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 · ‎2020-09-02

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

View solution in original post

uroboros · ‎2020-09-01

Hi @patricesalem , What are you looking for?

Please attach some data example of your origins, with some example that represent what are you looking for.

Kushal_Chawda · ‎2020-09-01

@patricesalem Does your ID_Order is unique in Invoices and credit notes table?

patricesalem · ‎2020-09-02

Hello

@Kushal_Chawda and @uroboros : thanks to both for your support.

I've tried to simplify my data model for the explanation. The ID_Order is indeed unique in both tables (it gathers the order number and the line number of the order).
what I'm looking to do:

table A

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

Table B

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

Table A will be completed by data coming from table B. Any Table B order_id not available in table A will be concatenated/added in table A. The result will look like:

Final Table (table A in my data model)

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

Kushal_Chawda · ‎2020-09-02

I think you just need full outer join like below

[Order_Entries]:
ID_Order,
Customer_Number,
Delivery_Town,
Qty
Sales
FROm Table;

join(Order_Entries)
LOAD
ID_Order,
Invoice_Type,
Amount
FROM Table;

patricesalem · ‎2020-09-02

I tried with your code that but then it only adds common records. Here is the code which is nearly the same than yours :

This is an extract of the section containing the order entry table

[SALES(ZSQ042)]:
LOAD 
	'type_sales' as TYPE_cde,
        num([COMMANDE])&'_'&num([POSTE]) as ID_ORDER,
	[ORIGINE]
	,TEXT(CODE_CLIENT) AS CODE_CLIENT
	,text ([CODE_CLIENT_LIVRE]) as [CODE_CLIENT_LIVRE]
	,[HIERARCHIE]
	,[DATE_CREATION_POSTE] 	  as [D_SALES]
	,[POSTE]
	,[BUSINESS_UNIT] as [Business unit],
	,[COMMANDE]
	,[NET_POSTE]
	,[QTE_POSTE]
    ;

In the other section:

join([SALES(ZSQ042)])
[FACTURATION]:
LOAD 
   
    num(COMMANDE)&'_'& num(POSTE) as ID_ORDER,
        //num(COMMANDE)&'_'& num(POSTE) as ID_ORDER1, 
    COMMANDE AS COMMANDE_INVOICE,
    POSTE  AS POSTE_INVOICE,
    FACTURE  AS FACTURE_INVOICE,
    TYPE_FACTURE  AS TYPE_FACTURE_INVOICE,
    ARTICLE AS ARTICLE_INVOICE,
    DÉSIGNATION AS DESIGNATION_INVOICE,
    HIERARCHIE AS HIERARCHIE_INVOICE,
    QTE_FACTURE AS QTE_FACTURE_INVOICE,
    NET as NET_INVOICE,

    DATE_FACTURE as DATE_FACTURE_INVOICE
FROM [lib://Facturation (xxx)/FACTURATION.xlsx] (ooxml, embedded labels, table is data);

So my code is equivalent to yours.

What I wish to get is all ID_ORDER from FACTURATION that have no match in [SALES] to be concatenated into SALES

That's my relation table

marcus_sommer · ‎2020-09-02

The easiest way to merge such tables is just to concatenate them. No risk to lose or duplicate any records through the joins and no special efforts to connect the tables with the other parts of the datamodel if you would keep multiple associated fact-tables.

- Marcus

uroboros · ‎2020-09-02

Hi @patricesalem , You need to make two steps.
First you need to Join the tables.

Later you can concatenate your results, with no exists; something like this:

[Order_Entries]:
ID_Order,
Customer_Number,
Delivery_Town,
Qty
Sales
FROm Table;
join(Order_Entries)
LOAD
ID_Order,
Invoice_Type,
Amount
FROM Table;

Concatenate(Order_Entries)
LOAD
ID_Order,
Invoice_Type,
Amount
FROM Table
Where not exists(ID_Order);

At the end of your execution, you will have your table as you need.

♥

patricesalem · ‎2020-09-02

jus tried, I end up with an error related to the concatenation :

L'erreur suivante s'est produite:
Field 'ID_ORDER' not found
Emplacement de l'erreur:
Concatenate [SALES(ZSQ042)]:
LOAD 
   
    num(COMMANDE)&'_'& num(POSTE) as ID_ORDER,
        
    COMMANDE AS COMMANDE_INVOICE,
    POSTE  AS POSTE_INVOICE,
    FACTURE  AS FACTURE_INVOICE,
    TYPE_FACTURE  AS TYPE_FACTURE_INVOICE,
    ARTICLE AS ARTICLE_INVOICE,
    DÉSIGNATION AS DESIGNATION_INVOICE,
    HIERARCHIE AS HIERARCHIE_INVOICE,
    QTE_FACTURE AS QTE_FACTURE_INVOICE,
    NET as NET_INVOICE,
    PORT as PORT_INVOICE,
    PAYEUR as Nr_Client_INVOICE,
    DATE_FACTURE as DATE_FACTURE_INVOICE
FROM [lib://Facturation (xxx)/FACTURATION.xlsx] (ooxml, embedded labels, table is data)
Where not exists(ID_Order)

I can't understand as the mentionned field exists :

LOAD 
	'type_sales' as TYPE_cde,
    num([COMMANDE])&'_'&num([POSTE]) as ID_ORDER,
	[ORIGINE]
	,TEXT(CODE_CLIENT) AS CODE_CLIENT
	,text ([CODE_CLIENT_LIVRE]) as [CODE_CLIENT_LIVRE]
	,[HIERARCHIE]
	,[CODE_ARTICLE]
	,[DESIGNATION_ARTICLE]
	,[DATE_CREATION_POSTE] 	  as [D_SALES]
	,[POSTE]
	,[MODELE_TRACTEUR]
	,[BUSINESS_UNIT] as [Business unit],
	,[NET_POSTE]
	,[QTE_POSTE]
    ;
    

[SALES(ZSQ042)]:
SELECT "ORIGINE"
	,"COMMANDE"
	,"POSTE"
	,"CODE_CLIENT_LIVRE"
	,"HIERARCHIE"
	,"CODE_ARTICLE"
	,"DESIGNATION_ARTICLE"
	,"DATE_CREATION_POSTE"
	,"DATE_MODIFICATION_POSTE"
	,"COMMANDE_CLIENT"
	,"MOTIF_BLOCAGE_LIVRAISON"
	,"NET_POSTE"
	,"QTE_POSTE"

FROM "QLIK"."ZSQ042"
;

In another section, I operate the join and concatenation :

join [SALES(ZSQ042)]:
LOAD 
   
    num(COMMANDE)&'_'& num(POSTE) as ID_ORDER,
     COMMANDE AS COMMANDE_INVOICE,
    POSTE  AS POSTE_INVOICE,
    FACTURE  AS FACTURE_INVOICE,
    TYPE_FACTURE  AS TYPE_FACTURE_INVOICE,
    ARTICLE AS ARTICLE_INVOICE,
    QTE_FACTURE AS QTE_FACTURE_INVOICE,
    NET as NET_INVOICE
    FROM [lib://Facturation (xxx)/FACTURATION.xlsx] (ooxml, embedded labels, table is data);


Concatenate [SALES(ZSQ042)]:
LOAD 
   num(COMMANDE)&'_'& num(POSTE) as ID_ORDER,
     COMMANDE AS COMMANDE_INVOICE,
    POSTE  AS POSTE_INVOICE,
    FACTURE  AS FACTURE_INVOICE,
    TYPE_FACTURE  AS TYPE_FACTURE_INVOICE,
    ARTICLE AS ARTICLE_INVOICE,
    QTE_FACTURE AS QTE_FACTURE_INVOICE,
    NET as NET_INVOICE
FROM [lib://Facturation (xxx)/FACTURATION.xlsx] (ooxml, embedded labels, table is data)
Where not exists(ID_ORDER);

patricesalem · ‎2020-09-02

@marcus_sommer ,

I have also tried your solution but for a reason I ignore, data coming from "facturation" is not added to my sales table.

Concatenate [SALES(ZSQ042)]:
load
    num(COMMANDE)&'_'& num(POSTE) as ID_ORDER,
    COMMANDE AS COMMANDE_INVOICE,
    POSTE  AS POSTE_INVOICE,
    FACTURE  AS FACTURE_INVOICE,
    TYPE_FACTURE  AS TYPE_FACTURE_INVOICE,
    ARTICLE AS ARTICLE_INVOICE,
    DÉSIGNATION AS DESIGNATION_INVOICE,
    HIERARCHIE AS HIERARCHIE_INVOICE,
    QTE_FACTURE AS QTE_FACTURE_INVOICE,
    NET as NET_INVOICE,
    PORT as PORT_INVOICE,
    PAYEUR as Nr_Client_INVOICE,
    DATE_FACTURE as DATE_FACTURE_INVOICE

FROM [lib://Facturation (xxxx)/FACTURATION.xlsx] (ooxml, embedded labels, table is data);

the result is only showing ID_ORDER from the sales table :

the sales table is generated in a previous section as follow

LOAD 
	'type_sales' as TYPE_cde,
    num([COMMANDE])&'_'&num([POSTE]) as ID_ORDER,
	[ORIGINE]
	,TEXT(CODE_CLIENT) AS CODE_CLIENT
	,text ([CODE_CLIENT_LIVRE]) as [CODE_CLIENT_LIVRE]
	,[HIERARCHIE]
	,[CODE_ARTICLE]
	,[DESIGNATION_ARTICLE]
	,[DATE_CREATION_POSTE] 	  as [D_SALES]
	,[POSTE]
	,[MODELE_TRACTEUR]
	,[BUSINESS_UNIT] as [Business unit],
	,[COMMANDE]
	,[COMMANDE_CLIENT]
	,[MARQUE_TRACTEUR]
	,[GAMME_TRACTEUR]
	,[VERSION_TRACTEUR]
	,[LANGUE]
	,[NET_POSTE]
	,[QTE_POSTE]
    ;
    

[SALES(ZSQ042)]:
SELECT "ORIGINE"
	,"COMMANDE"
	,"POSTE"
	,"CODE_CLIENT_LIVRE"
	,"HIERARCHIE"
	,"CODE_ARTICLE"
	,"DESIGNATION_ARTICLE"
	,"DATE_CREATION_POSTE"
	,"DATE_MODIFICATION_POSTE"
	,"COMMANDE_CLIENT"
	,"MOTIF_BLOCAGE_LIVRAISON"
	,"NET_POSTE"
	,"QTE_POSTE"
	,"MARQUE_TRACTEUR"
	,"GAMME_TRACTEUR"
	,"VERSION_TRACTEUR"
	,"MODELE_TRACTEUR"
	,"CODE_CLIENT"
	,"LANGUE"
	,"SECTEUR"
	,"BUSINESS_UNIT"
	,"ZONE_LIV"
FROM "QLIK"."ZSQ042"
;

I don't see why it does not concatenate...

any idea ?