Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Join Help Needed

Hello, I am very new to qlik and am not good with script. I want to be able to "Join" 3 different columns. I was told that I probably need an "inner" join but I am not sure.

My Email, Customer Type and Phone columns from both tables have data in both, different data or data in one or the other.



The emails above are fake, obviously I want to join the columns, not what is just shown in the example.

Below is my script stuff, I have no idea how to write this join script. I tried using the documentation but failed numerous times. If someone could help me write the script for the email column, I can then apply it to the other columns I need. Thanks!


LOAD [Company],

Company as ACompany,


  [Marketing Segment],

  [Customer Type] as ACustomerType,

  [Primary Contact (Account)],

  [Email] as AEmail,

  [Phone] as APhone,

  [Account Manager] as AAccountManager,


  [Gross Profit],

  [Status Type],

  [PO Value],

  [Actual Close Date],

  [Actual Revenue],


FROM [lib://qlikid_shaebert/Account_test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);


LOAD [First Name],

  [Last Name],


  [Event Type],

  [Account Manager],

  [Job Title],

  [Company]as ACompany,


  [Event Style],

  [Customer Type]

FROM [lib://qlikid_shaebert/Raw_Inport_Test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);

RENAME TABLE [Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682] TO [Sheet1];

RENAME TABLE [Sheet1_9788250d-be08-350b-01fc-01f33868] TO [Sheet1-1];

3 Replies

Re: Join Help Needed

This is an example; I made the assumption there is a common field between the 2 tables (User); I used the common field to join the 2 tables, so I can choose one of the 2 email values (bold)

// first table


load * inline [

User, Email

u1, u1@qlik.com

u2, u2@qlik.com

u3, u3@qlik.com



join (T1)

// second table

load User, Email as Email2 inline [

User, Email

u2, u2@qlik.com

u3, u333@qlik.com

u4,  u4@qlik.com

u5,  u5@qlik.com


Left Join (T1)



  // peek the Email if not empty, else Email2

  if(len(trim(Email))>0, Email, Email2) as NewEmail

Resident T1;

Not applicable

Re: Join Help Needed

Thank you for the help, but I have a couple questions...

  if(len(trim(Email))>0, Email, Email2) as NewEmail

^ Do I have to replace Email and Email2 with the data I included in my post? (Email, AEmail)

u1, u1@qlik.com

u2, u2@qlik.com

u3, u3@qlik.com



^ By using this code I will be able to apply this Join to the whole column, all rows? I need a code that does not include the example emails, the email addresses are unknown to me and are being pull from one table or the other. 

Again, the @qlik.com examples were only examples, I have hundreds of rows of information that needs to work for and the emails have different domains (@qlik.com,@gmail.com,@yahoo.com, etc). Will I have to do something like...

u1, ""

u2, ""

u3, ""

u4, ""

u5, ""


u150, ""

u151 ""


Id prefer not to have to do that because my maximum number of rows will change over time.

Not applicable

Re: Join Help Needed

specifically I need to Join two columns from 2 different tables.