Joining 2 fields from 2 tables

[shakeeb_mohammed]

Hi Community,

Hoping someone can assist me with the below-

I have 2 tables that contain similar fields - e.g-

Table 1 - 

Date

Supplier 

Orders

Table 2-

Date

Customer

Orders 

Effectively the supplier and customer need to be joined - both tables contain different supplier names , what I want to do is join this so when supplier is selected it will show supplier/customer and it’s relevant data. 

[ogster1974]

Orders:

Load

Date,

Supplier,

Orders

From table 1;

Concatenate (Orders)

Load

Date,

Customer,

Orders

From table 2;

Regards

Andy

[Vegar · Accepted solution]

You have two possible solutions in QlikView/Qlik Sense.

You could join your tables together using JOIN LOAD.

//This code will generate the green table in the picture below
Transactions:
LOAD * FROM Supplier.qvd (qvd);  //Yellow data in picture
JOIN (Transactions) LOAD * FROM Customer.qvd (qvd); ////Blue data in picture

Alternative you could choose to generate a concatenated transaction table. Stacking the data into the same table resulting a data table similar to the combined blue/yellow table in the picture

//This code will generate the blue/yellow table
Transactions:
LOAD * FROM Supplier.qvd (qvd);  //Yellow data in picture
CONCATENATE (Transactions) LOAD * FROM Customer.qvd (qvd); //blue data in picture

The third option is to keep the two tables as they are, two seperate tables. You'll get a syntetic key that you don't need to worry about, or you could eliminate the syntetic key by replacing the two Date and Order-fields with an composite key eg

LOAD:
Date & '|' & Order AS %Key,
Supplier
FROM Supplier.qvd (qvd);

LOAD 
Date & '|' & Order AS %Key,
Customer
FROM Customer.qvd (qvd);

Cheers

Vegar

[shakeeb_mohammed]

Hi Thanks for the info below -

I'm having some problems trying to solve this issue - i Should have mentioned in my original post -

Table 1 - comes from Microsoft SQL Server

Date

SupplierName

OrderQty

PlanQty

ActualQty

Table 2 - Comes from Excel spreadsheet

Date

Supplier

OrderQty

PlanVolQty

I have created another Excel Spreadsheet that merges the supplier name from Table 2 to match with Table 1 -

E.g -

Supplier(Table2)Customer Description	Company Description	SupplierName(Table 1)
F001-Aldi	Aldi	Aldi
F065 - APS Produce	Aps Produce	Aps Produce
F041 - Lactalis	Lactalis McLelland Ltd	Lactalis McLelland Ltd
F061 - Castle MacLellan	Primula	Primula

As the above table shows the customer Discription will not always match SupplierName.

So i have 3 tables that i need to join.

[Vegar]

You could use your manual defined table as a source for two mapping tables renaming the Supplier company name. Take a look at my screenshot below.