Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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:
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
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 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:
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];
@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:
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
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];