Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have below Table model :-
My Question is how to make the 3 Box combine into one Box. So that it look very neat and nice.
Below is the script :-
// Step 1 Create Raw Data
Input:
LOAD * INLINE [
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
];
// Step 2 Remove input table and Create Tmp Table and rename email field to email list. remove repeated email address.
Tmp:
load [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;
// Step 3 Remove Tmp table and Create a Raw Date table for load one distinct company with email listing.
[Raw data]:
noconcatenate
load distinct [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 [Customer/Vendor],[Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];
drop table [Raw data];
Input_Sales:
LOAD * INLINE [
Customer/Vendor, Customer Name,Row Total (SGD)
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,7
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,6
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01999-USD,CONVERGENT SYSTEMS (S) PTE LTD
];
Hope some one can advise.
Paul
like ?
Input:
LOAD * INLINE [
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
];
// Step 2 Remove input table and Create Tmp Table and rename email field to email list. remove repeated email address.
Tmp:
load [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;
// Step 3 Remove Tmp table and Create a Raw Date table for load one distinct company with email listing.
[Raw data]:
noconcatenate
load distinct [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 [Customer/Vendor],[Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];
drop table [Raw data];
join
LOAD * INLINE [
Customer/Vendor, Customer Name,Row Total (SGD)
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,7
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,6
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01999-USD,CONVERGENT SYSTEMS (S) PTE LTD
];
like ?
Input:
LOAD * INLINE [
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
];
// Step 2 Remove input table and Create Tmp Table and rename email field to email list. remove repeated email address.
Tmp:
load [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;
// Step 3 Remove Tmp table and Create a Raw Date table for load one distinct company with email listing.
[Raw data]:
noconcatenate
load distinct [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 [Customer/Vendor],[Customer Name],[E-Mail List],if(len(Ctmp)=0,'Y','') as Flag resident [Raw data];
drop table [Raw data];
join
LOAD * INLINE [
Customer/Vendor, Customer Name,Row Total (SGD)
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,7
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,6
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
CET01999-USD,CONVERGENT SYSTEMS (S) PTE LTD
];
wow very interesting , just replace with Join and it work. you are ready expert.