topic Re: Substring Count in QlikView

Substring Count

krishna20 — Sat, 16 Nov 2024 19:14:10 GMT

Hi Folks,

I have data like below and expected output as given.

I am using substring count to achieve this, but not getting expected result. Somewhere going wrong, Could anyone please look into this and advise.

Error_Count	Count
.,Kalyan	2
Uday@Gmail.com	1
Uday.,	2
Kalyan_krishna	1
Gopi23	2
(krishna	1

Load* ,
If(
( Substringcount(Error_Count, '.')
+Substringcount(Error_Count, ',')
+ Substringcount(Error_Count, '@')
+ Substringcount(Error_Count, '_')
+ Substringcount(Error_Count, '2')
+ Substringcount(Error_Count, '3')
+ Substringcount(Error_Count, '(')
),1,0) as Count;

Regards

Krishna

Re: Substring Count

Vegar — Fri, 24 Jan 2020 14:47:15 GMT

Maybe this approach using KeepChar() could help you?

LOAD Error_Count, Count as [Desired count], len(KeepChar(Error_Count,'.,@_23(')) as [Calculated count] inline [ Error_Count Count .,Kalyan 2 Uday@Gmail.com 1 Uday., 2 Kalyan_krishna 1 Gopi23 2 (krishna 1 ] (delimiter is ' ');

Notice that the "Uday@Gmail.com" counts 2 not 1 as in your table as it contains both . and @.

Re: Substring Count

m_woolf — Fri, 24 Jan 2020 14:56:11 GMT

Get rid of the IF and the 0,1:

Substringcount(Error_Count, '.')
+ Substringcount(Error_Count, ',')
+ Substringcount(Error_Count, '@')
+ Substringcount(Error_Count, '_')
+ Substringcount(Error_Count, '2')
+ Substringcount(Error_Count, '3')
+ Substringcount(Error_Count, '(') as Count

Re: Substring Count

krishna20 — Fri, 24 Jan 2020 15:17:02 GMT

Hi vegar,

Thanks for your reply. Actually the substring count flag is not showing right sum . Please look into below script and scenario and advise.

Load

If(Match(Left([Error_Count],1),'.',',','\','1','2','3','4','5','6','7','8','9','0','@','*','#','$','!','%','^','&','(',')',';','?','+','=','{','}','[',']','~','`','/',':','<','>','_')
or Match(Mid([Error_Count],1),',','\','1','2','3','4','5','6','7','8','9','0','@','*','#','$','!','%','^','&','(',')',';','?','+','=','{','}','[',']','~','`','/',':','<','>','_')
or Match(Right([Error_Count],1),'\','1','2','3','4','5','6','7','8','9','0','@','*','#','$','!','%','^','&','(',')','?','+','=','{','}','[',']','~','`','/',':','<','>','_'),
( Substringcount([Error_Count], '.')
+Substringcount([Error_Count], ',')
+ Substringcount([Error_Count], '@')
+ Substringcount([Error_Count], '1')
+ Substringcount([Error_Count], '2')

+ Substringcount([Error_Count], '<')

+ Substringcount([Error_Count], '>')

+ Substringcount([Error_Count], '_')
as , ,'.,\1234567890@*#$!%^&()?+={}[]~`/:<>_'))) as Error_Count;

It creates a flag with different numbers.

But When I sum this flag in straight table, the calculation

The count suppose to show 6 but it's showing 234 and many are there like this.

The Highlighted name has ,.<@.> and count should show as 6.

Re: Substring Count

Vegar — Mon, 27 Jan 2020 08:20:14 GMT

By the looks of your screenshot it looks strange. Could it be that you have multiple transactions with that email adress? (234/6 =39)

I also noticed something odd in your expression, is the yellow marking made by me correct?

You could also try to break out parts of our expression in order to identify which part of the expression is generating the large value.

/Vegar

Re: Substring Count

krishna20 — Wed, 29 Jan 2020 13:48:04 GMT

Yes , the else condition was wrong and it's modified. But the logic which have written to find the junk characters is not working properly.