Field Transforamtion

[mario-sarkis]

hey all ,

my question is that I have a field like this : DORAUSD or DORALBP I need my output to be without USD or LBP in this case will be DORA below is my data:

INPUT	Solution
AZMILBP	AZMI
AZMIUSD	AZMI
BABIDRISSLBP802	BABIDRISS
BABIDRISSUSD801	BABIDRISS
BDDLBP	BDD
BDDUSD	BDD
DORALBP	DORA
DoraLBP	DORA
DORAUSD	DORA
DoraUSD	DORA
FurnElChebbackUSD	FURNELCHEBAK
HADATHLBP	HADATH
HadathLBP	HADATH
HADATHUSD	HADATH
HadathUSD	HADATH
JDEIDEHLBP	JDEIDEH
JDEIDEHUSD	JDEIDEH
KHALDEHLBP	KHALDEH
KHALDEHUSD	KHALDEH
NACCACHELBP	KHALDEH
NaccacheLBP	NACCACHE
NACCACHEUS	NACCACHE
NaccacheUS	NACCACHE
RAMLETELBAYDALBP	RAMLETELBAYDA
RAMLETELBAYDAUSD	RAMLETELBAYDA
SAIDAEASTLBP	SAIDAEAST
SAIDAEASTUSD	SAIDAEAST
SASSINEUSD	SASSINE
TABARISLBP	TABARIS
TABARISUSD	TABARIS
ZALKALBP	ZALKA
ZALKAUSD	ZALKA
ZeidaniehLBP	ZEIDANIEH
ZeidaniehUSD	ZEIDANIEH
ZGHARTAEBLBP	ZHARTA

[tresesco]

Try like:

Upper(Replace(Replace(INPUT,'LBP',''),'USD','')) as OUTPUT

Edit: Upper() was missed

[antoniotiman]

You need add Upper(....) function

[prieper]

UPPER(REPLACE(Replace(Replace(INPUT,'LBP',''),'USD','', 'US', '')) as OUTPUT

to cover also the case of NaccacheUS

HTH Peter

[prma7799]

Hi Mario ,

Please try this

upper(PurgeChar(Replace(Replace(INPUT,'LBP',''),'USD',''),'8012'))  as NewField

Thanks

[Frank_Hartmann]

see attached qvw.

Replace(Replace(Replace(UPPER(KeepChar(REPLACE(Replace(Replace(INPUT,'LBP',''),'USD',''), 'US', ''),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')),'BB','B'),'ZGH','ZH'),'AEB','A') as OUTPUT1,
PurgeChar(Replace(Replace(Replace(UPPER(REPLACE(Replace(Replace(INPUT,'LBP',''),'USD',''), 'US', '')),'BB','B'),'ZGH','ZH'),'AEB','A'),'8012') as OUTPUT2,

hope this helps

[Anonymous]

Hi Mario,

Try this:

Purgechar(Replace(Replace(UPPER(INPUT),'LBP',''),'USD',''),'0123456789')  as Solution

Purgechar deletes the numbers chars, and Replace change USD and LBP to empty string.

But, if you have on INPUT, for example, one name like SASUSDINEUSD, and you want SASUSDINE I think that it is better this:

LOAD

     *,

     IF(RIGHT(INPUT,3) = 'USD' OR RIGHT(INPUT,3)='LBP', LEFT(INPUT,LEN(INPUT)-3),

          IF(RIGHT(INPUT,2) = 'US' OR RIGHT(INPUT,2)='LB', LEFT(INPUT,LEN(INPUT)-2),

               INPUT))               as Solution

     //I have seen that on NACCACHE you wanna delete US, and I have added two chars option.

;

LOAD

     ...,

     UPPER(TRIM(INPUT))               as INPUT,

     ...,

FROM...;

DROP Field INPUT FROM YourTable;

Regards!!!

[vinieme12]

Try as below

load *,UPPER(subfield(subfield(SubField(INPUT,'USD',1),'US',1),'LBP',1)) as CLEANED inline [

INPUT,Solution

AZMILBP,AZMI

AZMIUSD,AZMI

BABIDRISSLBP802,BABIDRISS

BABIDRISSUSD801,BABIDRISS

BDDLBP,BDD

BDDUSD,BDD

DORALBP,DORA

DoraLBP,DORA

DORAUSD,DORA

DoraUSD,DORA

FurnElChebbackUSD,FURNELCHEBAK

HADATHLBP,HADATH

HadathLBP,HADATH

HADATHUSD,HADATH

HadathUSD,HADATH

JDEIDEHLBP,JDEIDEH

JDEIDEHUSD,JDEIDEH

KHALDEHLBP,KHALDEH

KHALDEHUSD,KHALDEH

NACCACHELBP,KHALDEH

NaccacheLBP,NACCACHE

NACCACHEUS,NACCACHE

NaccacheUS,NACCACHE

RAMLETELBAYDALBP,RAMLETELBAYDA

RAMLETELBAYDAUSD,RAMLETELBAYDA

SAIDAEASTLBP,SAIDAEAST

SAIDAEASTUSD,SAIDAEAST

SASSINEUSD,SASSINE

TABARISLBP,TABARIS

TABARISUSD,TABARIS

ZALKALBP,ZALKA

ZALKAUSD,ZALKA

ZeidaniehLBP,ZEIDANIEH

ZeidaniehUSD,ZEIDANIEH

ZGHARTAEBLBP,ZHARTA

];