Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
Below is the post i ask about how to Flag company without email address :-
Below is the post i ask about how to combine 2 email address into 1 :-
Now i try to combine the above 2 solution into 1. i just cannot make it work :-
I have below script Part 1 and 2. Both have similar below loading of raw data.
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;
when i run individual part 1 or 2 script it work fine.
But when i combine together it cannot work. Hope some one can advise me how to combine part 1 and 3 script into one.
Part 1 - below Script working fine for combine 2 email into 1 :-
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;
[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 1 result - it will list out total 5 row od email is correct :-
contact@gvt.com.sg
ganesh@macroelectricals.com
kimhong.chong@gvt.com.sg
Mark.Meng@br-automation.com, Grace.Toh@br-automation.com
sales@supercom.com.sg
Part 2 Script script work fine for Flag those company with out email :-
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;
left join
load [Customer Name],
count(Email) as Ctmp
resident [Raw data]
where len(trim(Email))>0
group by [Customer Name] ;
Final:
load [Customer Name],Email,if(len(Ctmp)=0,'Y','') as Flag
resident [Raw data];
Part 2 Result :- Flag 4 Row is row is correct.
Flag Customer Name
B & R INDUSTRIAL AUTOMATION PTE LTD
GRAND VENTURE TECHNOLOGY LIMITED
SUPER COMPONENTS (S) PTE LTD
Y
Y CONVERGENT SYSTEMS (S) PTE LTD
Y MACRO AUTOMATION & ELECTRICALS PTE LTD
Y PHOENIX COMMUNICATIONS PTE LTD
Paul Yeo
Same answer
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];
@paulyeo11 can you share a sample data and the expected output to test something I can't open your zip file !
@paulyeo11 can you share the input and the expected here in the forum 🙂
Hi Taoufiq
Below is the raw data for DB_SD_.CSV :-
Customer/Vendor | Customer Name | ||
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 |
Below is DB_PM_.CSV
Customer/Vendor | Customer Name | ||
VET00002-EUR | B & R INDUSTRIAL AUTOMATION PTE LTD | Mark.Meng@br-automation.com, Grace.Toh@br-automation.com | |
VES00013-SGD | PHOENIX COMMUNICATIONS PTE LTD | ||
VET00037 | CONVERGENT SYSTEMS (S) PTE LTD | ||
CET00403-SGD | MACRO AUTOMATION & ELECTRICALS PTE LTD | ganesh@macroelectricals.com |
Part 1 result - it will list out total 5 row od email is correct :-
contact@gvt.com.sg
ganesh@macroelectricals.com
kimhong.chong@gvt.com.sg
Mark.Meng@br-automation.com, Grace.Toh@br-automation.com
sales@supercom.com.sg
Part 2 Result :- Flag 4 Row is row is correct.
Flag Customer Name
B & R INDUSTRIAL AUTOMATION PTE LTD
GRAND VENTURE TECHNOLOGY LIMITED
SUPER COMPONENTS (S) PTE LTD
Y
Y CONVERGENT SYSTEMS (S) PTE LTD
Y MACRO AUTOMATION & ELECTRICALS PTE LTD
Y PHOENIX COMMUNICATIONS PTE LTD
Hi Taoufiq
Above link is my question. Hope you can take a look.
Paul
Same Answer :
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];
Same answer
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];
Thank you very very much