Case 911 How to combine two email address ?

[paulyeo11]

Hi All

I have below raw data  :-

Customer/Vendor	Customer Name	Email	E-Mail
CET00837-SGD	SUPER COMPONENTS (S) PTE LTD	sales@supercom.com.sg	sales@supercom.com.sg
CET00837-SGD	SUPER COMPONENTS (S) PTE LTD	sales@supercom.com.sg
CET01610-USD	GRAND VENTURE TECHNOLOGY LIMITED	contact@gvt.com.sg
CET01610-USD	GRAND VENTURE TECHNOLOGY LIMITED	contact@gvt.com.sg	kimhong.chong@gvt.com.sg

After load the above raw data into Qlik View using below script :-

Directory;
LOAD [Customer/Vendor],
[Customer Name],
Email,
[E-Mail]
FROM
DB_SD_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

May i know how to create a report to list the email address as below :-

sales@supercom.com.sg
contact@gvt.com.sg
sales@supercom.com.sg
kimhong.chong@gvt.com.sg

Paul Yeo

[rodjager · Accepted solution]

Hi Paul,

There are going to be a few ways of doing this.

The quickest I can put together is this, however, I am sure someone will have a more elegant solution shortly.

Hope this helps.

Rod

[Raw data]:
LOAD 
	[Customer/Vendor], 
     [Customer Name], 
     Email, 
     [E-Mail]
FROM
[https://community.qlik.com/t5/New-to-QlikView/Case-911-How-to-combine-two-email-address/td-p/1739739]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

[Email List]:
LOAD 
	[Customer/Vendor],
	Email						as [E-Mail List]
RESIDENT [Raw data]
WHERE LEN(TRIM(Email)) > 0;

LOAD 
	[Customer/Vendor],
	[E-Mail]					as [E-Mail List]
RESIDENT [Raw data]
WHERE LEN(TRIM([E-Mail])) > 0;

[paulyeo11]

Hi Rod , 

Thank you very much for your help. it work fine now .

Now i have one more question , how to flag those email , so there is not duplication. The final result look like below :-

Flag	E-Mail List	Customer Name
Yes	contact@gvt.com.sg	GRAND VENTURE TECHNOLOGY LIMITED
Yes	kimhong.chong@gvt.com.sg	GRAND VENTURE TECHNOLOGY LIMITED
	contact@gvt.com.sg	GRAND VENTURE TECHNOLOGY LIMITED
	kimhong.chong@gvt.com.sg	GRAND VENTURE TECHNOLOGY LIMITED
Yes	sales@supercom.com.sg	SUPER COMPONENTS (S) PTE LTD
	sales@supercom.com.sg	SUPER COMPONENTS (S) PTE LTD

Hope you can advise me again.

Paul 

[rodjager]

Hi Paul,

If I understand correctly, you want to remove any duplicates you see if you combine the original table with the new E-Mail list field?

One option is to remove the original email fields i.e add the following as the last line of code to the sample I provided.

Drop Fields [E-Mail], [Email];

 Alternatively, just creating a table the fields [Customer Name]  and [E-Mail List] should give you the unique combinations (without duplicates), a filter object just for [E-Mail List] will also display just the unique values.

Hope this helps.

Rod

[paulyeo11]

Dear Dodjager

Thank you very much.

Paul yeo