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

[paulyeo11]

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 Name	MODEL	item_cost	Doc_Num	Flag_D	Remark
GRAND VENTURE TECHNOLOGY LIMITED	BANANA	3	2	Y	Doc_Num 2 one of the item no cost
GRAND VENTURE TECHNOLOGY LIMITED	NSI	0	2	Y	Doc_Num 2 one of the item no cost
SUPER COMPONENTS (S) PTE LTD	APPLE	4	1	N	Doc_Num 1 Both item have cost
SUPER COMPONENTS (S) PTE LTD	NSI	8	1	N	Doc_Num 1 Both item have cost

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

Paul

[Kushal_Chawda · Accepted solution]

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