How to Create field = Flag_Sales , using load script for those company have sales >0 ?

[paulyeo11]

Hi

Hi All

How to Create field = Flag_Sales , using load script for those company have sales >0 ?

map:
mapping LOAD *
Inline [
CUSTCode, CUSTAttribute
1 ,CUSTOMER
2 ,SUPPLIER
3 ,INTER COY
] ;

Input_Sales:

LOAD *,
ApplyMap('map', wildmatch( [Customer/Vendor], 'CET*','VIT*','LOA*')) as CUST_TYPE INLINE [
Customer/Vendor, Customer Name,Row Total (SGD)
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,7
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,6
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
LOA01999-USD, CONVERGENT SYSTEMS (S) PTE LTD,3
];

The expected result :-

Customer Name	Row Total (SGD)	Flag_Sales
CONVERGENT SYSTEMS (S) PTE LTD	3	Y
GRAND VENTURE TECHNOLOGY LIMITED
SUPER COMPONENTS (S) PTE LTD	7	Y
SUPER COMPONENTS (S) PTE LTD	6	Y

[jwjackso · Accepted solution]

map:
mapping LOAD *
Inline [
CUSTCode, CUSTAttribute
1 ,CUSTOMER
2 ,SUPPLIER
3 ,INTER COY
] ;

Input_Sales:
LOAD *,
ApplyMap('map', wildmatch( [Customer/Vendor], 'CET*','VIT*','LOA*')) as CUST_TYPE INLINE [
Customer/Vendor, Customer Name,Row Total (SGD)
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,7
CET00837-SGD, SUPER COMPONENTS (S) PTE LTD,6
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
LOA01999-USD, CONVERGENT SYSTEMS (S) PTE LTD,3
];
Join (Input_Sales)
Load [Customer/Vendor],
If(Sales_Total > 0,'Y','N') as Flag_Sales;
Load [Customer/Vendor],
Sum([Row Total (SGD)]) as Sales_Total
Resident [Input_Sales] group by [Customer/Vendor];

[paulyeo11]

Thank you sir