topic Re: trim, subfield issue in QlikView

trim, subfield issue

saurabh5 — Tue, 02 Dec 2014 19:19:08 GMT

hi All,

i have a field named cust_name_key, with values as follows:

Abraham,Janet (A526243)

Kaur,Jaswin (CX12345)

Tammy,Silo (C213211)

lampo,Shawn(lil) Adams(D343432)

the requirement is to get to fields name and key

name key

Abraham,Janet A526243

Kaur,Jaswin CX12345

Tammy,Silo C213211

Lampo,Shawn (lil) Adams D343432

in order to extract name i used

subfield(cust_name_key,right(cust_name_key,9)) as name,

it works but couldnt extract key as some names had brackets in them and chr lenght may vary, can sumebody suggest something?

its_anandrjs — Tue, 02 Dec 2014 19:45:29 GMT

Hi Saurabh,

Try this way i am loading your table from the excel source file you can load your table because there is comma( , ) in the data.

Try this

Source:

LOAD

cust_name_key,

subfield(cust_name_key,right(cust_name_key,9)) as name,

PurgeChar(right(cust_name_key,9),'(,)') as Key;

LOAD cust_name_key

FROM

[NameSource.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

LOAD * Resident Source Where Len(name)>0;

DROP Table Source;

Regards

Anand

saurabh5 — Tue, 02 Dec 2014 20:05:17 GMT

Thanks Anand, the purgechar function works fine...

Regards

Saurabh