Case 917 How to Flag Company with out email address ?

[paulyeo11]

Hi All

I have below raw data :-

Customer Name		E-Mail List
B & R INDUSTRIAL AUTOMATION PTE LTD		Mark.Meng@br-automation.com
CONVERGENT SYSTEMS (S) PTE LTD
GRAND VENTURE TECHNOLOGY LIMITED		contact@gvt.com.sg
GRAND VENTURE TECHNOLOGY LIMITED		kimhong.chong@gvt.com.sg
MACRO AUTOMATION & ELECTRICALS PTE LTD		ganesh@macroelectricals.com
PHOENIX COMMUNICATIONS PTE LTD
SUPER COMPONENTS (S) PTE LTD		sales@supercom.com.sg

The result result table i want to get is below :-

Customer Name		E-Mail List		Flag
B & R INDUSTRIAL AUTOMATION PTE LTD		Mark.Meng@br-automation.com
CONVERGENT SYSTEMS (S) PTE LTD				Y
GRAND VENTURE TECHNOLOGY LIMITED		contact@gvt.com.sg
GRAND VENTURE TECHNOLOGY LIMITED		kimhong.chong@gvt.com.sg
MACRO AUTOMATION & ELECTRICALS PTE LTD		ganesh@macroelectricals.com
PHOENIX COMMUNICATIONS PTE LTD				Y
SUPER COMPONENTS (S) PTE LTD		sales@supercom.com.sg

For Each i in 'SD_','PM_'

[Raw data]:
LOAD
'$(i)' as SOURCCE,
[Customer/Vendor],
[Customer Name],
Email,
[E-Mail]
FROM
DB_$(i).csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next i;

// Part 1 :- Combine Email and E-Mail into one Email working fine.

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

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

// Part 2 Flag company no email address , Keep get error msg cannot find field [E-Mail List]

left join

load [Customer Name],

count([E-Mail List]) as Ctmp

resident [Raw data]

where len(trim([E-Mail List]))>0

group by [Customer Name] ;

Final:

load [Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag_No_Email

resident [Raw data];

Below is the error msg i get :-

Field not found - <E-Mail List>
left join

load [Customer Name],

count([E-Mail List]) as Ctmp

resident [Raw data]

where len(trim([E-Mail List]))>0

group by [Customer Name]

May i know where go wrong ?

Below link consists of 2 sample data and one QVW file :-

https://www.dropbox.com/sh/cemfs7i8esmx284/AAAcLQWIeoCm6DTlB6Oj1H7va?dl=0

Paul

[lironbaram]

hi 

you use the raw data table in your left join call 

but i think you want to use the Email List table 

you don't have the field Email List in your raw data table 

[paulyeo11]

Hi Sir

May I know how to combine below table into one table ?

Raw Data
Email List

[jmmolero]

Hi

You can concatenate result of each iteration to a "dummy" table:

[Raw data]:

Load * inline [

dummy

0

];

For Each i in 'SD_','PM_'

 Concatenate([Raw data])
    LOAD
    '$(i)' as SOURCCE,
    [Customer/Vendor],
    [Customer Name],
    Email,if(len(trim([E-Mail List]))=0,'Y','') as  as Flag_No_Email,
   [E-Mail]
   FROM
   DB_$(i).csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next i;

Drop field dummy;

[paulyeo11]

Hi Sir

I don’t understand what you mean. Can you share a little bit more ?

Paul

[Taoufiq_Zarra]

@paulyeo11  maye be :

[Raw data]:

LOAD * INLINE [
    Customer Name, E-Mail List
    B & R INDUSTRIAL AUTOMATION PTE LTD, Mark.Meng@br-automation.com
    CONVERGENT SYSTEMS (S) PTE LTD,  
    GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg
    GRAND VENTURE TECHNOLOGY LIMITED, kimhong.chong@gvt.com.sg
    MACRO AUTOMATION & ELECTRICALS PTE LTD, ganesh@macroelectricals.com
    PHOENIX COMMUNICATIONS PTE LTD,  
    SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg
];

left join 

load [Customer Name],count([E-Mail List]) as Ctmp resident [Raw data] where len(trim([E-Mail List]))>0 group by [Customer Name] ;

Final:

load [Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];

drop table [Raw data];

output:

[paulyeo11]

Hi Taoufiq

Thank you for your help.

I need to do combine 2 column of email into one email using the below script , before i check which company no email address.

// Part 1 :- Combine 2 email address into one working fine.

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

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

[Taoufiq_Zarra · Accepted solution]

@paulyeo11  Maye be this :

Input table :

Script:

Input:

LOAD * INLINE [
    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
    CET01999-USD,CONVERGENT SYSTEMS (S) PTE LTD
];

Tmp:

load [Customer/Vendor], [Customer Name], Email as [E-Mail List] resident Input ;
concatenate load  [Customer/Vendor], [Customer Name], [E-Mail] as [E-Mail List]   resident Input ;

drop table Input;

[Raw data]:
noconcatenate

load distinct [Customer/Vendor], [Customer Name],[E-Mail List] resident Tmp;

drop table Tmp;


left join 

load [Customer Name],count([E-Mail List]) as Ctmp resident [Raw data] where len(trim([E-Mail List]))>0 group by [Customer Name] ;

Final:

load [Customer/Vendor],[Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];

drop table [Raw data];

output:

[paulyeo11]

Hi Taoufiq

Thank you very much , it work fine now . 

However my actual raw data field need to load include all below :- 

For Each i in 'PM','LE','DA','PW','SD'

Input:

LOAD
'$(i)' as SOURCE,
[Customer/Vendor] AS [Customer_Vendor],
[Customer/Vendor],
[Customer Name] AS [Customer_Name],
[Customer Name],
[Payment Terms] AS [Payment_Terms],
SlpName,
[Industry Description] AS Industry_Description,
[Sub-Industry],
[Mailing Street] AS Mailing_Street,
[Mailing Street 1] AS Mailing_Street_1,
[Mailing City],
[Mailing State],
[Mailing Country] AS Mailing_Country,
[Mailing Zip],
Phone,
Fax,
Email, // EMAIL FROM COMPANY
Email AS EMAIL_COY,
[Credit Limit] AS Credit_Limit,
[BP Code] AS BP_Code,
[BP Name] AS BP_Name,
[Contact Person Name] AS Contact_Person_Name,
Position,
[Mobile Phone] AS Mobile_Phone,
[Telephone 1] AS Telephone_1,
[Telephone 2],
[Contact Fax],
[E-Mail] AS EMAIL_CON, // EMAIL FROM CONTACT
[E-Mail] AS EMAIL2,
[E-Mail],
[Block Sending Marketing Content] as OPT_OUT,
Active as ACTIVE,
[Sales Employee]
FROM
$(vRAWPath)DB_$(i).CSV
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i;

It there any way i can get all the other field like SOURCE into the FINAL table ?

Now Final Table only have below 4 field :-

[Customer/Vendor]

[Customer Name]

Flag

E-Mail List

Paul

[Taoufiq_Zarra]

@paulyeo11 

If I understood correctly, you can add others Fileds in red position like :

Input:

LOAD * INLINE [
[Others Fields .....], 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
CET01999-USD,CONVERGENT SYSTEMS (S) PTE LTD
];

Tmp:

load [Others Fields .....],[Customer/Vendor], [Customer Name], Email as [E-Mail List] resident Input ;
concatenate load [Customer/Vendor], [Customer Name], [E-Mail] as [E-Mail List] resident Input ;

drop table Input;

[Raw data]:
noconcatenate

load distinct [Others Fields .....],[Customer/Vendor], [Customer Name],[E-Mail List] resident Tmp;

drop table Tmp;

left join

load [Customer Name],count([E-Mail List]) as Ctmp resident [Raw data] where len(trim([E-Mail List]))>0 group by [Customer Name] ;

Final:

load [Others Fields .....],[Customer/Vendor],[Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];

drop table [Raw data];