topic Re: Count with keep char in QlikView

Count with keep char

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on this.

From the below Name field how to get the count of alphanumeric records(SONY1,JOHN235 -count with only caps letters)

and all CAPS letter records count(HONY,PHILIPS)

Name

SONY1

JOHN235

HONY

PHILIPS

abc

xysdz

Thanks in advance.

Re: Count with keep char

tresB — Thu, 07 Dec 2017 10:33:35 GMT

=Count( If(Upper(KeepChar(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))=Name, Name))

OR just,

=Count( If((KeepChar(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')=Name, Name))

Re: Count with keep char

OmarBenSalem — Thu, 07 Dec 2017 10:36:16 GMT

names:

load *, if(Name = UpperName, 1,0) as Flag;

load *, PurgeChar(Name,'abcdefghijklmnopqrstuvwxyz1234567890') as UpperName Inline [

Name

SONY1

JOHN235

HONY

PHILIPS

abc

xysdz

];

and in ur expression:

count({<Flag={1}>}Name)

Re: Count with keep char

kaushiknsolanki — Thu, 07 Dec 2017 10:37:18 GMT

Try this.

Load *,Len(KeepChar(Name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) as Count inline [

Name

SONY1

JOHN235

HONY

PHILIPS

abc

xysdz

];

Regards,

Kaushik Solanki

Re: Count with keep char

bhavvibudagam — Thu, 07 Dec 2017 11:16:08 GMT

Hi Kaushik,

Thanks for your reply.How to get the alpha numeric count in UI.

example in the above inline

SONY1

JOHN235 are alpahnumeric.

I need to get the count as 2.

Re: Count with keep char

kaushiknsolanki — Thu, 07 Dec 2017 11:22:28 GMT

Try this

=Sum(Aggr(if(isnum(KeepChar(Name,'0123456789')) and IsText(Name),1,0),Name))

I am checking if it is num and text both, if yes then count.

Regards,

Kaushik Solanki

Re: Count with keep char

OmarBenSalem — Thu, 07 Dec 2017 12:30:10 GMT

I always prefer to create things in script; to use in the charts; It prevent Qlik from calculating each expression :

names:

load *, if(Name = UpperName, 1,0) as Flag , if (Name <> UpperName and len(Trim(UpperName))<>0,1,0) as FlagAlphaNum;

load *, PurgeChar(Name,'abcdefghijklmnopqrstuvwxyz1234567890') as UpperName Inline [

Name

SONY1

JOHN235

HONY

PHILIPS

abc

xysdz

];

with this, for alphaNum:

count({<FlagAlphaNum={1}>}Name)

for count cap:

count({<Flag={1}>}Name)