topic Case 931 How to make my table model look nice ? in QlikView

Case 931 How to make my table model look nice ?

paulyeo11 — Wed, 16 Sep 2020 11:39:33 GMT

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

Re: Case 931 How to make my table model look nice ?

Taoufiq_Zarra — Wed, 16 Sep 2020 11:53:58 GMT

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

Re: Case 931 How to make my table model look nice ?

paulyeo11 — Thu, 17 Sep 2020 01:21:56 GMT

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