topic Re: Join Help Needed in QlikView

Join Help Needed

Wed, 25 Nov 2020 16:16:04 GMT

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:

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];

Re: Join Help Needed

maxgro — Tue, 09 Aug 2016 22:09:20 GMT

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;

Re: Join Help Needed

Wed, 10 Aug 2016 13:36:58 GMT

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.

Re: Join Help Needed

Wed, 10 Aug 2016 13:57:23 GMT

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