Case 920 How to use Subfield to take out first 3 letter ?

[paulyeo11]

Hi All

I have below raw data :-

// Step 1 Create 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
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
VIT01610-USD, GRAND VENTURE TECHNOLOGY LIMITED
LOA01999-USD, CONVERGENT SYSTEMS (S) PTE LTD,3
];

/*

Step 2 i need create new field = CUST_TYPE , fIELD NAME = Customer/Vendor :-

CET = CUSTOMER
VIT = SUPPLIER
LOA = INTER COY

May i know how to do ?

Paul Yeo

[Taoufiq_Zarra · Accepted solution]

@paulyeo11 

One 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
];

output:

[paulyeo11]

Hi Sir

Thank you very much your script is very short.

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

For Each i in 'SD','PM'

[Raw data]:
LOAD
'$(i)' as SOURCCE,
[Customer/Vendor],
ApplyMap('map', wildmatch( [Customer/Vendor], 'CET*','VIT*','LIR*')) as CUST_TYPE,
[Customer Name],
Email,
[E-Mail]
FROM
DB_$(i).csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next i;