Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Case 939 How to Flag those Doc_Num = 2 ? AS one of the item without cost

Hi All

I have below load script :-

Input:

LOAD *,if(item_cost=0,'Y','N') as Flag INLINE [
Doc_Num,MODEL,item_cost,Customer/Vendor, Customer Name, Email, E-Mail
1,APPLE,4,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg, sales@supercom.com.sg
1,NSI,8,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg,
2,BANANA,3,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg,
2,NSI,0,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg, kimhong.chong@gvt.com.sg
];

I need to flag Doc_Num , when one of the item with out cost.

The end result , i hope to get :-

Customer NameMODELitem_costDoc_NumFlag_DRemark
GRAND VENTURE TECHNOLOGY LIMITEDBANANA32YDoc_Num 2 one of the item no cost
GRAND VENTURE TECHNOLOGY LIMITEDNSI02YDoc_Num 2 one of the item no cost
SUPER COMPONENTS (S) PTE LTDAPPLE41NDoc_Num 1 Both item have cost
SUPER COMPONENTS (S) PTE LTDNSI81NDoc_Num 1 Both item have cost

 

Hope some one can advise me , How to create a field name for Flag_D ?

Paul

1 Solution

Accepted Solutions
Kushal_Chawda

@paulyeo11  try below

Input:
LOAD * INLINE [
Doc_Num,MODEL,item_cost,Customer/Vendor, Customer Name, Email, E-Mail
1,APPLE,4,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg, sales@supercom.com.sg
1,NSI,8,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg,
2,BANANA,3,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg,
2,NSI,0,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg, kimhong.chong@gvt.com.sg
];

Left Join(Input)
LOAD Distinct Doc_Num,
     'Y' as Flag
Resident Input
Where item_cost=0;

Final:
NoConcatenate
LOAD Doc_Num,
     MODEL,
     item_cost,
     [Customer/Vendor], 
     [Customer Name], 
     Email, 
     if(len(trim(Flag))=0,'N',Flag) as Flag,
     [E-Mail]
Resident Input;

DROP Table Input;

View solution in original post

1 Reply
Kushal_Chawda

@paulyeo11  try below

Input:
LOAD * INLINE [
Doc_Num,MODEL,item_cost,Customer/Vendor, Customer Name, Email, E-Mail
1,APPLE,4,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg, sales@supercom.com.sg
1,NSI,8,CET00837-SGD, SUPER COMPONENTS (S) PTE LTD, sales@supercom.com.sg,
2,BANANA,3,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg,
2,NSI,0,CET01610-USD, GRAND VENTURE TECHNOLOGY LIMITED, contact@gvt.com.sg, kimhong.chong@gvt.com.sg
];

Left Join(Input)
LOAD Distinct Doc_Num,
     'Y' as Flag
Resident Input
Where item_cost=0;

Final:
NoConcatenate
LOAD Doc_Num,
     MODEL,
     item_cost,
     [Customer/Vendor], 
     [Customer Name], 
     Email, 
     if(len(trim(Flag))=0,'N',Flag) as Flag,
     [E-Mail]
Resident Input;

DROP Table Input;

View solution in original post