topic Need help joining 2 columns from 2 tables into one in QlikView

Need help joining 2 columns from 2 tables into one

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need help joining 2 columns from 2 tables into one

Anonymous — Fri, 12 Aug 2016 14:32:01 GMT

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

Re: Need help joining 2 columns from 2 tables into one

Gysbert_Wassenaar — Fri, 12 Aug 2016 14:36:59 GMT

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

Re: Need help joining 2 columns from 2 tables into one

effinty2112 — Fri, 12 Aug 2016 14:55:14 GMT

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

Re: Need help joining 2 columns from 2 tables into one

Fri, 12 Aug 2016 15:41:17 GMT

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

Re: Need help joining 2 columns from 2 tables into one

Anonymous — Mon, 15 Aug 2016 06:16:12 GMT

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!