Create a Join with specific fields

[ksomosera10]

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!

[surendraj]

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.

[kaushiknsolanki]

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]

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]

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

[ksomosera10]

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

[ksomosera10]

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

[kaushiknsolanki]

Hi,

It will come from table 1.

Regards,

Kaushik Solanki

[ksomosera10]

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]

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