Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Partner
Partner

help with subfield

Hi all,

I have a field (@8) which contains user logon names, but they are a mixture of formats. i.e.

NT-DOMAIN\FirstnameLastname

NT-DOMAIN\R_FirstnameLastname

I have used the following to strip out the domain and \ so i just end up with the FirstnameLastname

lower(SubField(@8,'\',2)

this works great for the majority of the logon names but it leaves a few that are :

R_FirstnameLastname.

Can anyone tell me how to also strip out the R_ all in the same expression?

note: R_ is only one, there are other letters too with underscores (so not always R)

Thanks

1 Solution

Accepted Solutions
Highlighted
jerem1234
Valued Contributor II

Re: help with subfield

You can try:

=mid(SubField(@8,'\',2), index(SubField(@8,'\',2), '_')+1)

Hope this helps!

View solution in original post

13 Replies
Highlighted
MVP
MVP

Re: help with subfield

Hi

Just nest the subfields, like this:

=SubField(SubField(@8,'\',2) '_', 2)

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: help with subfield

Hi

why not only

=SubField(@8, '_', 2)   which give FirstnameLastname

best regards

Chris

Highlighted
Partner
Partner

Re: help with subfield

Thanks Jonathan, but that fails to reload. it says its missing a close bracket, although it looks correct to me.

also, i still need to make the field lower case.

Highlighted
sunilkumarqv
Valued Contributor II

Re: help with subfield

try this

if you want to purge certain character then use below

purgechar ( 'NT-DOMAIN\R_FirstnameLastname','R_' ) returns ''NT-DOMAIN\FirstnameLastname'

or

Trim(Replace(Replace(Replace(SubField(@8, '\', 2), 'R_FirstnameLastname', 'FirstnameLastname'))) AS ColumnField

Highlighted
Partner
Partner

Re: help with subfield

Hi christian,

that would work if all of the logon names were in the format of

NT-DOMAIN\R_FirstnameLastname.

That doesnt work for the ones in the format of

NT-DOMAIN\FirstnameLastname.

Highlighted
Partner
Partner

Re: help with subfield

Hi Sunil,

Thanks, but as i said not all are in the format of R_, some are C_, A_ etc.

I thought i could wrap another subfield around my current one like Jonathan suggested, but i can't get it to work

Highlighted
sunilkumarqv
Valued Contributor II

Re: help with subfield

try this

if you want to purge certain character then use below

purgechar ( 'NT-DOMAIN\R_FirstnameLastname','R_' ) returns ''NT-DOMAIN\FirstnameLastname'

or

Trim(Replace(Replace(Replace(SubField(@8, '\', 2), 'R_F', 'F'), 'C_F', 'F'), 'A_', 'F')) AS imex


u may continue in these manner

Highlighted
jerem1234
Valued Contributor II

Re: help with subfield

You can try:

=mid(SubField(@8,'\',2), index(SubField(@8,'\',2), '_')+1)

Hope this helps!

View solution in original post

Highlighted

Re: help with subfield

Hi,

Try this

=SubField(SubField(@8,'\',2) ,'_', 2)

Great dreamer's dreams never fulfilled, they are always transcended.