Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ksomosera10
Not applicable

Create a Join with specific fields

Hi,

Im new at Qlik Sense and Im wondering if you could help me with my problem. Does anyone here know how to join two tables using 'Data Load Editor' on Qlik Sense? Does Qlik Sense Join can behave like SQL? BTW, Im using Excel File with 900K rows FYI. For further information to my question, here is my sample:

***Here I want to use this table, named Table1

[Table1]:

LOAD

[Name]

[Nickname]

[Address]

[Number]

[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

***And join it to Table2, Containing

[Table2]:

LOAD

[Name] AS Name[

[Address]

[Bank No]

[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security);

**Using Name and Address only...

NOTE: I don't want to use Qlik Sense auto-join because it returns with "Synthetic Key"

THANK YOU!

1 Solution

Accepted Solutions
surendraj
Not applicable

Re: Create a Join with specific fields

LinkTable:

Load

Distinct

Key,

[Name],

[Address]

Resident

Table1;

Concatenate

Load

Distinct

Key,

[Name],

[Address]

Resident

Table2;

(Or)

You can load entire tables(table 1 and table2) and make a comment(//) to other fields except(Key,[Name],[Address]) as below...

link_table:

[Name],

//[Nickname]

[Address],

[Name]'&'[Address] as key

//[Number]

//[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

concatinate(link_table)

LOAD

[Name],

[Address],

[Name]'&'[Address] as key

//[Bank No],

//[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security)

Here what happens is these 3 fields(Key,[Name],[Address]) are concatinated by make use of table 1 and table2.these table(link table)has records which are available in both the tables.

So abviously you can acess the name field form link table only.because,In table1 and table 2 name filed is not vailable.(we are commented).

Hope this help you!!!

20 Replies
surendraj
Not applicable

Re: Create a Join with specific fields

Try with link table....to aviod synthatic key.

[Table1]:

LOAD

//[Name]

[Nickname],

//[Address]

[Name]'&'[Address] as key,

[Number],

[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

[Table2]:

LOAD

//[Name]

//[Address]

[Name]'&'[Address] as key,

[Bank No],

[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security)

link_table:

[Name],

//[Nickname]

[Address],

[Name]'&'[Address] as key

//[Number]

//[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

concatinate(link_table)

LOAD

[Name],

[Address],

[Name]'&'[Address] as key

//[Bank No],

//[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security)

By Link table...we have Name,Address and key.

If you want to extract some other fields,those are available in [Table1] and [Table2]

Here we have data model with 3 tables..[Table1],[Table2] and Link table with no synthetic keys.

kaushik_solanki
Not applicable

Re: Create a Join with specific fields

Hi,

You can try this script.

[Table1]:

LOAD

[Name]&[Address]as Key,

[Name]

[Nickname]

[Address]

[Number]

[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

***And join it to Table2, Containing

[Table2]:

LOAD

[Name]&[Address]as Key,

[Bank No]

[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security);

You can search for how to remove synthetic Key and you will find many more ways of doing it.

Regards,

Kaushik Solanki

rahulpawarb
Not applicable

Re: Create a Join with specific fields

Hello Kevin,

Trust that you are doing good!

Below are two approaches you can use to solve your problem:

1. Using JOIN (In below given example I have used INNER JOIN; you can make use LEFT/RIGHT/JOIN as per your need)

[Table1]:

LOAD

[Name],

[Nickname],

[Address],

[Number],

[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

INNER JOIN (Table1)

[Table2]:

LOAD

[Name] AS Name,

[Address],

[Bank No],

[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security);

2. Creating Composite/Complex Key (In below given example I have created composite key using Name and Address; This will eliminate the Synthetic key issue)

[Table1]:

LOAD

[Name] & '|' & [Address] Key,

[Nickname],

[Number],

[Birthday]

FROM [lib://DOCUMENTS/data.xlsx]

(ooxml, embedded labels, table is data);

[Table2]:

LOAD

[Name] & '|' & [Address] Key,

[Name] AS Name,

[Address],

[Bank No],

[Bank Type]

FROM [lib://DOCUMENTS/security.xlsx]

(ooxml, embedded labels, table is security);

Hope this will be of help.

Regards!

Rahul

ksomosera10
Not applicable

Re: Create a Join with specific fields

How can I use or access the "Name" or "Address" when I need them? I cannot see it, only the "key" THANKS!

ksomosera10
Not applicable

Re: Create a Join with specific fields

How can I use or access the "Name" or "Address" when I need them? I cannot see it, only the "key" THANKS!

ksomosera10
Not applicable

Re: Create a Join with specific fields

How can I use or access the "Name" or "Address" when I need them? I cannot see it, only the "key" THANKS!

kaushik_solanki
Not applicable

Re: Create a Join with specific fields

Hi,

It will come from table 1.

Regards,

Kaushik Solanki

ksomosera10
Not applicable

Re: Create a Join with specific fields

Yes, it is. but my problem is I don't know my expression now. Since it was changed to "Key" it changed my table. My old expression was this:

SUM({<[Address]={'New York'}>} [Savings])

Now, I don't know how to access "address" ONLY since it is now named: "Key"

NOTE: I forgot to say "Savings" field was on table2. It contains many fields that I just randomly pick some for presentation purposes.

rahulpawarb
Not applicable

Re: Create a Join with specific fields

Hello Kevin,

As you can see, Table2 contains Name & Address fields you can refer them. You can keep Name & Address fields in Table1 or Table2 and refer then later.

Regards!

Rahul