topic [ASK] how to count certain character? in QlikView

[ASK] how to count certain character?

Mon, 15 Nov 2010 04:31:24 GMT

hello..

i wanna ask, how to count certain character in a word?

example, i have few records in a column

columnA

-------------

mine.A

mine.A.B

bye.A

bye.A.B.C

thanks.A

thanks.A.A.C.D

and i want to know hos much dot (.) character show in every records

how can i do that in qlikview?

many thx...

[ASK] how to count certain character?

Mon, 15 Nov 2010 06:17:01 GMT

You want to count the dots??

[ASK] how to count certain character?

Mon, 15 Nov 2010 06:19:39 GMT

@mOngkz

yes i do

but i dont know the function to do it

can anyone give me a hand please..

many thx...

[ASK] how to count certain character?

Mon, 15 Nov 2010 06:56:22 GMT

use subfield function:

Directory;

LOAD Word,SubField(Word,'.') as sub

FROM

[source.xlsx]

(ooxml, embedded labels, table is Sheet2);

in the expression tab:

Count(sub) - 1..because eventually the number of words is greater than the number of dots..if the dots are used as delimiter

[ASK] how to count certain character?

Mon, 15 Nov 2010 07:16:57 GMT

you can also use this in your expression: TextCount(SubField(Word,'.'))-1

to count the dots/delimiter.

[ASK] how to count certain character?

Mon, 15 Nov 2010 07:37:02 GMT

@mongkz

it almost rite, but doesnt rite

some times it tells two dots when the word have more then two dots

i really stuck n need a help really

😞

many many thx...

[ASK] how to count certain character?

Mon, 15 Nov 2010 07:46:53 GMT

@richardo:

meaning your not using the dots as a delimiter??

try using this one(expression): =Len(Word)-Len(Replace(Word,'.',''))

[ASK] how to count certain character?

Mon, 15 Nov 2010 08:34:00 GMT

@mongkz

it's perfect

thx a lot mongkz

really many thx to u

[ASK] how to count certain character?

Mon, 15 Nov 2010 08:52:07 GMT

I'am glad that I helped you.. I am new to qlikview too so I use this forum as my training ground.

[ASK] how to count certain character?

johnw — Tue, 16 Nov 2010 02:01:41 GMT

I suspect this would work more efficiently:

substringcount(ColumnA,'.')

[ASK] how to count certain character?

Wed, 30 May 2012 15:29:29 GMT

Awesome, this solution from John also works and its simpler.