Count with keep char

[bhavvibudagam]

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.

[tresesco]

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

OR just,

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

[OmarBenSalem]

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)

[kaushiknsolanki]

Try this.

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

Name

SONY1

JOHN235

HONY

PHILIPS

abc

xysdz

];

Regards,

Kaushik Solanki

[bhavvibudagam]

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.

[kaushiknsolanki · Accepted solution]

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

[OmarBenSalem]

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)