Hi All
Below is the post i ask about how to Flag company without email address :-
https://community.qlik.com/t5/QlikView-Scripting/Case-912-Flag-company-with-out-email-address/m-p/17...
Below is the post i ask about how to combine 2 email address into 1 :-
https://community.qlik.com/t5/New-to-QlikView/Case-911-How-to-combine-two-email-address/m-p/1740100#...
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
