Case 912 Flag company with out email address

[paulyeo11]

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	Email	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

[Taoufiq_Zarra · Accepted solution]

@paulyeo11 

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:

[Kushal_Chawda]

@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);

[paulyeo11]

Hi Kuah

Thank you very much for your sharing .

Your script will flag email field is blank.
But my requirement is not that .
For example company name = AZONIC , it have 2 contact , one of the contact have email , so I don’t need to flag.

The objective to is make sure every company in my database , must have one email address.

Paul Yeo

[paulyeo11]

Hi Toau

Look like you understand my requirement to search for company with out one email address.

I like to know how you import the raw data ? I mean [Raw data] , I don’t think you manual typing word by word , or did you test your script and see it is working ? I mean if I run the whole script , will it be able find out any syntax error ? As now I don’t have note book with to test.

Paul Yeo

[paulyeo11]

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.

https://community.qlik.com/t5/QlikView-Scripting/Case-913-Flag-company-with-out-any-email-addtess-us...

Paul

[Taoufiq_Zarra]

Hi @paulyeo11 

I missed your message due to the notification bug.
but I see that you have solved the problem !