Case 913 Flag company with out any email addtess using FOR and I

[paulyeo11]

Hi All

I have below script work fine :-

For Each i in 'PM','SD'
[Raw data]:
LOAD
'$(i)' AS SOURCE,
[Customer/Vendor],
[Customer Name],
Email
FROM
DB_$(i)_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next i;

// below script work fine last time , but now cannot work :-

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];

Hope some one can advise me where go wrong ?

Paul Yeo

[kaushiknsolanki]

Hi,

What is your expected output, as per the data Qlik shows right result.

[Kushal_Chawda]

@paulyeo11  Is it due to there is synthetic key? Can you elaborate what is not working?

Remove synthetic key by  replacing below code (between /*....*/) with code highlighted after that

/* Final:

load [Customer Name],Email,if(len(Ctmp)=0,'Y','') as Flag

resident [Raw data]; */

left join

load [Customer Name],if(len(Ctmp)=0,'Y','') as Flag

resident [Raw data];

[paulyeo11]

Hi Kush

I think yesterday i was confuse and mistaken that my load script is not working , actually i just check it work fine. Thank you very much , any way i have check your script , it work also.

Paul

[paulyeo11]

Hi Kush

I have try to work on flag company with out email address , i get error msg , hope you can advise me.

https://community.qlik.com/t5/QlikView-Scripting/Case-917-How-to-Flag-Company-with-out-email-address...

Paul

[Taoufiq_Zarra · Accepted solution]

@paulyeo11 

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];