Questien: how to count a string value

[Anonymous]

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

[vishsaggi]

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'

];

[its_anandrjs]

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"

];

[Anonymous]

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

[vishsaggi]

Where you want to display this result?

May be try this?

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

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

[its_anandrjs]

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)

[Anonymous]

i want to display it in a table like this

Name     Result

Klaus          2

Bernd          2

[Anonymous]

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

[its_anandrjs]

Share some sample file then look into that.

[techvarun]

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