Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
saurabh5
Contributor II

trim, subfield issue


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?

1 Solution

Accepted Solutions

Re: trim, subfield issue

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;

subfieldop.png

Regards

Anand

2 Replies

Re: trim, subfield issue

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;

subfieldop.png

Regards

Anand

saurabh5
Contributor II

Re: trim, subfield issue


Thanks Anand, the purgechar function works fine...

Regards

Saurabh

Community Browser