Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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.

Example:

example.PNG

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!

[Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682]:

LOAD [Company],

Company as ACompany,

  [Territory],

  [Marketing Segment],

  [Customer Type] as ACustomerType,

  [Primary Contact (Account)],

  [Email] as AEmail,

  [Phone] as APhone,

  [Account Manager] as AAccountManager,

  [Topic/Project],

  [Gross Profit],

  [Status Type],

  [PO Value],

  [Actual Close Date],

  [Actual Revenue],

  [Currency]

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

(ooxml, embedded labels, table is Sheet1);

[Sheet1_9788250d-be08-350b-01fc-01f33868]:

LOAD [First Name],

  [Last Name],

  [Email],

  [Event Type],

  [Account Manager],

  [Job Title],

  [Company]as ACompany,

  [Phone],

  [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
MVP
MVP

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

T1:

load * inline [

User, Email

u1, u1@qlik.com

u2, u2@qlik.com

u3, u3@qlik.com

u4,

];

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)

LOAD

  User,

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

u4,

];


^ 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.