Case 931 How to make my table model look nice ?

[paulyeo11]

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

[Taoufiq_Zarra · Accepted solution]

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

@paulyeo11 

[paulyeo11]

wow very interesting , just replace with Join and it work. you are ready expert.