topic Re: help with subfield in QlikView

help with subfield

hopkinsc — Wed, 12 Mar 2014 13:56:57 GMT

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

Re: help with subfield

jonathandienst — Wed, 12 Mar 2014 13:59:36 GMT

Just nest the subfields, like this:

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

HTH

Jonathan

Re: help with subfield

Wed, 12 Mar 2014 14:03:04 GMT

why not only

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

best regards

Chris

Re: help with subfield

hopkinsc — Wed, 12 Mar 2014 14:17:41 GMT

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.

Re: help with subfield

sunilkumarqv — Wed, 12 Mar 2014 14:18:21 GMT

try this

if you want to purge certain character then use below

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

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

Re: help with subfield

hopkinsc — Wed, 12 Mar 2014 14:19:35 GMT

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.

Re: help with subfield

hopkinsc — Wed, 12 Mar 2014 14:21:17 GMT

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

Re: help with subfield

sunilkumarqv — Wed, 12 Mar 2014 14:26:58 GMT

try this

if you want to purge certain character then use below

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

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

u may continue in these manner

Re: help with subfield

jerem1234 — Wed, 12 Mar 2014 14:31:28 GMT

You can try:

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

Hope this helps!

Re: help with subfield

PrashantSangle — Wed, 12 Mar 2014 14:38:25 GMT

Hi,

Try this

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

Re: help with subfield

SunilChauhan — Wed, 12 Mar 2014 14:44:50 GMT

Hi You can try This

=If(findoneof(@8,'_')>0,Subfield(@8,'_',2),Subfield(@8,'\',2))

either in script or in Expression you can use this

see the attched file

hope this helps

Re: help with subfield

Wed, 12 Mar 2014 14:52:07 GMT

this works.

if(left(@8,12)='NT-DOMAIN\R_',Subfield(@8,'_',2),subfield(@8,'\',2))

Re: help with subfield

maxgro — Wed, 12 Mar 2014 15:50:21 GMT

NT:

load

if(WildMatch(Result, '*_*'), Right(Result, len(Result) - Index(Result, '_')), Result) as Result,

Name;

load

Name,

SubField(Name,'\',2) as Result

;

load * inline [

Name

NT-DOMAIN\FirstnameLastname

NT-DOMAIN\FirstnameLastname2

NT-DOMAIN\FirstnameLastname3

NT-DOMAIN\R_FirstnameLastname4

NT-DOMAIN\R_FirstnameLastname5

NT-DOMAIN\R_FirstnameLastname6

NT-DOMAIN\R_FirstnameLastname7

NT-DOMAIN\RU_FirstnameLastname8

];

Re: help with subfield

MK_QSL — Wed, 12 Mar 2014 15:52:04 GMT

SubField(Replace(YourFieldName,'_','\'),'\',-1)