Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
Hi,
What is your expected output, as per the data Qlik shows right result.
@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];
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
Hi Kush
I have try to work on flag company with out email address , i get error msg , hope you can advise me.
Paul
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];