Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
=Count( If(Upper(KeepChar(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))=Name, Name))
OR just,
=Count( If((KeepChar(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')=Name, Name))
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)
Try this.
Load *,Len(KeepChar(Name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) as Count inline [
Name
SONY1
JOHN235
HONY
PHILIPS
abc
xysdz
];
Regards,
Kaushik Solanki
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.
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
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)