topic Re: Field Transforamtion in QlikView

Field Transforamtion

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Field Transforamtion

tresB — Thu, 27 Apr 2017 10:31:53 GMT

Try like:

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

Edit: Upper() was missed

Re: Field Transforamtion

antoniotiman — Thu, 27 Apr 2017 10:35:43 GMT

You need add Upper(....) function

Re: Field Transforamtion

prieper — Thu, 27 Apr 2017 10:39:08 GMT

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

to cover also the case of NaccacheUS

HTH Peter

Re: Field Transforamtion

prma7799 — Thu, 27 Apr 2017 10:47:57 GMT

Hi Mario ,

Please try this

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

Thanks

Re: Field Transforamtion

Frank_Hartmann — Thu, 27 Apr 2017 11:07:03 GMT

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

Re: Field Transforamtion

Anonymous — Thu, 27 Apr 2017 11:16:30 GMT

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!!!

Re: Field Transforamtion

vinieme12 — Thu, 27 Apr 2017 12:38:28 GMT

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

];