Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
i have below raw data , my load script as below :-
[Raw data]:
LOAD
[Customer/Vendor],
[Customer Name],
Email
FROM
DB_SD_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
May i know how to Flag those company with out email like below :-
Customer Name | Flag | |
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD | ||
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD | huiming@azonic.com.my | |
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD | pearly@azonic.com.my | |
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD | wcyeoh@azonic.com.my | |
GLAMCO AVIATION PTE LTD | Y | |
GRAND VENTURE TECHNOLOGY LIMITED | contact@gvt.com.sg | |
SCI AUTOMATION PTE. LTD. | Y | |
SUPER COMPONENTS (S) PTE LTD | sales@supercom.com.sg |
Paul Yeo
One solution :
[Raw data]:
LOAD * INLINE [
Customer Name, Email
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD,
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, huiming@azonic.com.my
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, pearly@azonic.com.my
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, wcyeoh@azonic.com.my
GLAMCO AVIATION PTE LTD,
GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg
SCI AUTOMATION PTE. LTD.,
SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg
];
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];
drop table [Raw data];
output:
One solution :
[Raw data]:
LOAD * INLINE [
Customer Name, Email
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD,
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, huiming@azonic.com.my
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, pearly@azonic.com.my
AZONIC AUTOMATION & SYSTEM TECHNOLOGY SDN BHD, wcyeoh@azonic.com.my
GLAMCO AVIATION PTE LTD,
GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg
SCI AUTOMATION PTE. LTD.,
SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg
];
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];
drop table [Raw data];
output:
@paulyeo11 you can simply flag those in same load statement it self like below
[Raw data]:
LOAD
[Customer/Vendor],
[Customer Name],
Email,
if(len(trim(Email))=0,'Y','') as Flag
FROM
DB_SD_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Hi Taoufiq
I have convert my load script to load multiple data file by using FOR and i and NEXT i; your load script for detect company no email address unable to work now.
Paul
Hi @paulyeo11
I missed your message due to the notification bug.
but I see that you have solved the problem !