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

[paulyeo11]

Hi Taoufiq

Thank you very much. It work fine now.

Paul

[paulyeo11]

Hi Taoufiq

i was waking up 5 Am to analyse how your script  manage to get the result i want. I notice some thing :-

- You know how to create a table and transform the table to another table.

- After the first 2 step i lost all the way till the end. need a lot visualization and imagination.  

- The reason you like to use inline load , is because you prefer to test with out load excel file ?

- This approach is unlike partial reload right ? Because you re create table and table again.

Paul