Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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;
@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;