Trim on Load-multiple trims of value

[Anonymous]

I have a field with data that needs to be trimmed in different places.  I have tried several SUBFIELD trims, but cannot get it to trim off the APJ-AP-EUR-USA-EM-AM data bits within the value.

Below are the results of a few SUBFIELD on the LOAD

 

  

Using  Mid([Target Job], 24,8) AS Region,

Thanks in advance for the help.

[sunny_talwar]

May be this:

MAPPING:

Mapping

LOAD * Inline [

Region, Check

APJ, @@@

AP, ###

EUR, $$$

USA, %%%

EM, ^^^

AM, &&&

];

MAPPING2:

Mapping

LOAD * Inline [

Check, Region

@@@, APJ

###, AP

$$$, EUR

%%%, USA

^^^, EM

&&&, AM

];

Table:

LOAD Field,

  ApplyMap('MAPPING2', KeepChar(MapSubString('MAPPING', Field), '@#$%^&')) as Region;

LOAD * Inline [

Field

WW_EQUAT_MEC1_EQUATWIN_APJ-SENDING-1

WW_EQUAT_MEC1_EQUATWIN_APJ-SENDING-2

WW_EQUAT_MEC1_EQUATWIN_APJ-SENDING-3

WW_EQUAT_MEC1_EQUATWIN_EUR-SENDING-1_TZE

WW_EQUAT_MEC1_EQUATWIN_EUR-SENDING-1_TZD

WW_EQUAT_MTH1_EQUATWIN_EUR-SENDING-2_TZE

WW_EQUAT_MTH1_EQUATWIN_EUR-SENDING-2_TZD

WW_EQUAT_MEC1_EQUATWIN_GERP_EUR_SENDING-1_TZE

WW_EQUAT_MTH1_EQUATWIN_GERP_EUR_SENDING-2_TZE

WW_EQUAT_MTH1_EQUATWIN_GERP_USA_SENDING-2_TZD

WW_EQUAT_MEC1_EQUATWIN_AM_WD3CLOSE_TZD

WW_EQUAT_MEC1_EQUATWIN_EUR_WD3CLOSE_TZD

WW_EQUAT_MEC1_EQUATWIN_GERP_APJ_SENDING-1

WW_EQUAT_MEC1_EQUATWIN_AP_WD3CLOSE

];

[maxgro · Accepted solution]

LOAD

       *,

       Pick(WildMatch(Field,

            '*_APJ_*', '*_APJ-*', '*_AP_*', '*_EUR_*', '*_EUR-*', '*_EU_*', '*_AM_*', '*_USA_*', '*_USA-*'),

            'APJ', 'APJ', 'APJ', 'EUR', 'EUR', 'EUR', 'AM', 'USA', 'USA') as Region

Inline [

Field

WW_EQUAT_MEC1_EQUATWIN_APJ-SENDING-1

WW_EQUAT_MEC1_EQUATWIN_APJ-SENDING-2

............

];