trim, subfield issue

[saurabh5]

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 · Accepted solution]

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]

Thanks Anand, the purgechar function works fine...

Regards

Saurabh