Need help joining 2 columns from 2 tables into one

[#1]

Hello, I have 2 tables (with over 2000 Company IDs) associated with each other using the Customer ID already set up in Qlik Sense. The problem is I have an email field in both tables that I want to combine in the Qlik sense table view.

Below are the 2 tables that I imported to Qlik.

Company Id and Email has the 2 same titles so in Qlik it creates a synthetic key and I end up having to rename one of the email columns. I understand I have to do that but, I don't what two different email fields.

Below is what I WANT to be the product of the two tables:

I have no Idea how to write code/script.

Below is the script:

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

LOAD [Company ID],

Company ID as ACompany ID,

  [Email] as AEmail,

  [Gross Profit],

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

(ooxml, embedded labels, table is Sheet1);

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

LOAD [Phone],

  [Email],

  [Event Type],

  [Company ID]as ACompany ID,

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

Can someone please show me what I have to write for a script for this to view the way I want it to? Thanks for any help, no one has been able to solve this for me.

[Anonymous]

Hey Shane!

It seems that you have to use LEFT JOIN statement.

For your case something like:

[Table1]:

Load

     [Company ID],

     [Email],

     [Gross Profit]

From

[lib://qlikid_shaebert/Account_test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

[Table2]:

LOAD

     [Phone],

     [Email],

     [Event Type],

     [Company ID]

From

[lib://qlikid_shaebert/Account_test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join ([Table1])

Load

     [Phone],

     [Email],

     [Event Type],

     [Company ID]

Resident [Table2];

Drop Table [Table2];

Try this thing. Hope it helps!

Elena

[Gysbert_Wassenaar]

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

LOAD [Company ID],

  [Email] as AEmail,

  [Gross Profit],

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

(ooxml, embedded labels, table is Sheet1);

JOIN

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

LOAD [Phone],

  [Email] as BEmail,

  [Event Type],

  [Company ID]

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

(ooxml, embedded labels, table is Sheet1);

Result:

LOAD *, If(Len(Trim(AEmail)), AEmail, BEmail) as Email

RESIDENT [Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682];

DROP TABLE [Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682]

DROP FIELDS AEmail, BEmail;

[effinty2112]

Hi Shane,

               I would make a separate table of your e-mail addresses to check that there are no contradictions between your two tables. something like this:

E-mail:

LOAD [Company ID],

  Email as Email1

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

(ooxml, embedded labels, table is Sheet1);

Left Join(E-mail)

LOAD

[Company ID],

  Email as Email2

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

(ooxml, embedded labels, table is Sheet1);

Left Join(E-mail)

LOAD

[Company ID]

if(Len(Email1)*Len(Email2)>0 and Email1<>Email2,'Mismatch','OK') as Discrepancy

Resident E-mail;

If there are no discrepancies then

MappingEmail:

Mapping

Load

[Company ID],

  Email1 & Email2

Resident E-Mail;

Now join your two tables without an e-mail field, say it's called NewTable. Now

Left Join(NewTable)

Load

Distinct //shouldn't be necessary

[Company ID],

ApplyMap('MappingEmail',[Company ID],'No e-mail address') as E-mail

Resident NewTable;

Good Luck

Andrew

[#5]

This almost worked. Referring to the picture examples: If table 2 had no value, the join would not return the value in table 1.

So Customer ID 6 ends up having no data for the email

[Anonymous]

Then it's only way to change the script like the following:

[Table1]:

Load

     [Company ID],

     [Email1],

     [Gross Profit]

From

[lib://qlikid_shaebert/Account_test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

[Table2]:

LOAD

     [Phone],

    [Email2],

     [Event Type],

     [Company ID]

From

[lib://qlikid_shaebert/Account_test_Updated.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join ([Table1])

Load

     [Phone],

     If(Len(Email1)<>0, Email1, Email2) as Email,

     [Event Type],

     [Company ID]

Resident [Table2];

Drop Table [Table2];

I hope this will help you, Shane!