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!

[ishanbhatt]

Hi Kevin,

Apply below script an it'll clear your all doubts.

1. LOAD 
2. [Name] & '|' & [Address] Key,
3. [Name],
4. [Address], 
5. [Nickname], 
6. [Number], 
7. [Birthday] 
8. FROM [lib://DOCUMENTS/data.xlsx] 
9. (ooxml, embedded labels, table is data); 
10.  
11. [Table2]: 
12. LOAD 
13. [Name] & '|' & [Address] Key, 
14. [Name], 
15. [Address], 
16. [Bank No], 
17. [Bank Type] 
18. FROM [lib://DOCUMENTS/security.xlsx] 
19. (ooxml, embedded labels, table is security);
20. 
    
21. LinkTable:
22. Load
23. Distinct
24. Key,
25. [Name],
26. [Address]
27. 
    Resident
28. Table1;
    
29. 
    
30. Concatenate
31. Load
32. Distinct
33. Key,
34. [Name],
35. [Address]
36. Resident
37. Table2;
38. 
    
39. Drop Fields Name, Address from Table1,Table2;
40. 
    
41. So Now, You'll get the NAme and Address information From the Link Table and Table1 and Table2 are connected with Link Through Link Table.
42. 
    
43. Hope this helpful
44. 
    
45. Ishan
    

[ksomosera10]

Thanks but it shows an error around Line 25 saying:

The following error occured:

Field 'Name' not found

The error occured here:

?

What should I do?

[ksomosera10]

Thanks! If I access these through table1 or table2, does the link works? like it will be aggregate also? 'cause I have many aggregate fields.

[rahulpawarb]

Link between Table1 and Table2 will work perfectly (by referring key field)!

[surendraj]

Your link table have 3 fields...

[Name],

[Address],

[Name]'&'[Address] as key You can access "Name" or "Address" when you need them..

By this link table you can made your data model perfectly...with out any synthetic keys.

[ksomosera10]

Thanks! what I mean is how I can call for example "Name" on my link table?

for example in SQL you call it by: table.Name

How about in Qlik Sense? I'm so sorry, I'm just a little bit confused, hope you all bear with me.

Thanks again.

[surendraj]

Please elaborate More..i didn't get you..

In link table itself you can use Name field...

[ksomosera10]

Okay, I want to know if I have table1 with: [Name] and [Address]

and then table2 with: [Name] and [Address] also..

and finally linktable with: [Name] and [Address] and [Key]

That's what I know, now how could I know that the [Name] that I call is from the linktable?
and not on the table only?

I hope I make myself clear... sorry.

[surendraj · Accepted solution]

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!!!

[ksomosera10]

Thanks! I think I already get what are you sayin' I will try this later!.