topic Questien: how to count a string value in QlikView

Questien: how to count a string value

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

Hi,

i am starting to work with qlikview a few weeks ago.

I am able to count all string values in a column but a Special value.

For exampel the column is named Members an in a row the values are:

Klaus

Klaus, Bernd, Olaf

Olaf

Bernd, Manfred

i want to count the Name Klaus.

How do i have to create the code in qlik view?

thank you for your help

Jay

Re: Questien: how to count a string value

vishsaggi — Mon, 02 Oct 2017 00:41:14 GMT

Try substringcount() function like if your field name is like Name

Will give you the count.

Use a straight table

Add Dim as your Name

Expr: = Substringcount(Name, 'Klaus')

At script level you can use the same like below:

LOAD *, Substringcount(Name, 'Klaus') AS StrCnt INLINE [

Name

'Klaus'

'Klaus, Bernd, Olaf'

'Olaf'

'Bernd, Manfred'

];

Re: Questien: how to count a string value

its_anandrjs — Mon, 02 Oct 2017 05:54:39 GMT

You can try with the Index function to find out the string "Klaus" in the rows of your field if you find then index mark it 1 in another column. Then by this, you are able to identify where is your string and then in UI you can count this also.

Tabe1:

LOAD *,Index(Name,'Klaus') as NameFlag Inline

[

Name

"Klaus"

"Klaus, Bernd, Olaf"

"Olaf"

"Bernd, Manfred"

];

Re: Questien: how to count a string value

Anonymous — Mon, 02 Oct 2017 12:00:18 GMT

At first thank you for you support.

What i need is just the value, how many Klaus are in my column

For exampel:

the column Name

Name

Klaus

Klaus, Bernd, Olaf

Olaf

Bernd, Manfred

Result:

Klaus: 2

Bernd: 2

Olaf is not necessary, i dont want to know how many Olaf there are.

Sorry for my bad english.

Best regards

jay

Re: Questien: how to count a string value

vishsaggi — Mon, 02 Oct 2017 15:53:00 GMT

Where you want to display this result?

May be try this?

= 'Klaus' & '=' & Sum(SubStringCount(Name, 'Klaus')) & Chr(13) &

'Bernd' & '=' & Sum(SubStringCount(Name, 'Bernd'))

Re: Questien: how to count a string value

its_anandrjs — Mon, 02 Oct 2017 16:18:27 GMT

Then Try this first remove space between Klaus, Bernd, Olaf and from all rows then use subfield to create the single rows for each and on UI take the count of the NewName field to find out the no. of presentation for Klaus and Bernd. For taking the count for the Klaus and Bernd use Set analysis on the straight table may you can use Substring count but it depends on how to use in the model

Tab1:

LOAD *,SubField(PurgeChar(Name,' '),',') as NewName;

LOAD *,Index(Name,'Klaus') as NameFlag Inline

[

Name

"Klaus"

"Klaus, Bernd, Olaf"

"Olaf"

"Bernd, Manfred"

];

Dim:- Name

Expre:- =Count( {<NewName={'Klaus','Bernd'}> } NewName)

Re: Questien: how to count a string value

Anonymous — Mon, 02 Oct 2017 16:36:42 GMT

i want to display it in a table like this

Name Result

Klaus 2

Bernd 2

Re: Questien: how to count a string value

Anonymous — Mon, 02 Oct 2017 17:01:31 GMT

Thank you Anand,

i can handle the first part:

Tab1:

LOAD *,SubField(PurgeChar(Name,' '),',') as NewName;

LOAD *,Index(Name,'Klaus') as NameFlag Inline

[

Name

"Klaus"

"Klaus, Bernd, Olaf"

"Olaf"

"Bernd, Manfred"

];

But i did not get the second result what you show in the second table with the headline Result.

i tried to creat a straight table with the field new name but it only shows the new name and not the results. What do i wrong?

regards

jay

Re: Questien: how to count a string value

its_anandrjs — Tue, 03 Oct 2017 02:59:50 GMT

Share some sample file then look into that.

Re: Questien: how to count a string value

techvarun — Tue, 03 Oct 2017 05:09:23 GMT

Tabe1:

LOAD *,SubField(PurgeChar(Name,'.,,'), ' ') as Word Inline

[

Name

"Klaus"

"Klaus, Bernd, Olaf"

"Olaf"

"Bernd, Manfred"

];

[WordCount]:

LOAD

Word,

count(Word) as WordCount

RESIDENT Tabe1

GROUP BY Word;

Drop Table Tabe1

Try the above script

Output

Re: Questien: how to count a string value

Anonymous — Wed, 04 Oct 2017 17:08:50 GMT

Dear Community, thanks to all of you.

I got a solution with this:

LOAD Subfield(Name,',') as Name

FROM ...;

Then i changed the Settings in the listbox and it counts.

This solution is okay for the first step.

Thanks a lot.

Best regards

jay