Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shakeeb_mohammed
Contributor III
Contributor III

Joining 2 fields from 2 tables

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. 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

ConcatenateLoad.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
ogster1974
Master II
Master II

Orders:

Load

Date,

Supplier,

Orders

From table 1;

Concatenate (Orders)

Load

Date,

Customer,

Orders

From table 2;

Regards

Andy

Vegar
MVP
MVP

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

ConcatenateLoad.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

shakeeb_mohammed
Contributor III
Contributor III
Author

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 DescriptionCompany Description SupplierName(Table 1)
F001-AldiAldiAldi

F065 - APS Produce

Aps ProduceAps Produce
F041 - LactalisLactalis McLelland LtdLactalis McLelland Ltd
F061 - Castle MacLellanPrimulaPrimula

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

So i have 3 tables that i need to join.

Vegar
MVP
MVP

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.

ConcatenateLoad.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes