topic SubStringCount in QlikView

SubStringCount

Anonymous — Thu, 18 Jan 2018 20:45:31 GMT

Can anyone please tell me why this SubStringCount command is selecting columns Department and Department Director

SubStringCount(Concat(Column),"Department Director')

and how to change it, to pull only the Department Director column

Where column is the name of my column in my inline table

thank

Re: SubStringCount

juraj_misina — Thu, 18 Jan 2018 20:54:24 GMT

Hi David,

I wouldn't expect this to count Department column. Are you sure "Department Director" column does not appear twice in your concatenated string? Double check by SubStringCount(Concat(distinct Column),'Department Director')

Hope this helps.

Juraj

Re: SubStringCount

Anonymous — Thu, 18 Jan 2018 20:58:49 GMT

Juraj,

positive, it like since the columns names are so similar it picking up both of them. I have another column that is doing the same thing too, Consult Hours Year 1 and Hours Year 1. But the rest of my 105 columns are working perfectly.

Re: SubStringCount

juraj_misina — Thu, 18 Jan 2018 21:07:27 GMT

Hi David,

in that case I'd try something like this, simply to make sure that my search string is not created by a random concatenation of values:

SubStringCount(Concat(distinct Column, '|')&'|','Department Director|')

Re: SubStringCount

Anonymous — Thu, 18 Jan 2018 21:15:26 GMT

Juraj

no change. My inline stream is like this

DynamicDimensions:
REPLACE LOAD
*
Inline [
%DimCategory,Column

R3, Department
R3, Department Director

];

if this helps. thank you

Re: SubStringCount

juraj_misina — Thu, 18 Jan 2018 21:28:41 GMT

David, it works perfectly with just these two lines of data, of course. Is this table connected to the rest of your data model? Is it possible that the value is counted twice because of incorrect association or some other reason on data model level?

Re: SubStringCount

sunny_talwar — Thu, 18 Jan 2018 22:26:16 GMT

Try this

SubStringCount(Concat(DISTINCT '|' & Column & '|'),'|Department Director|')

Re: SubStringCount

Anonymous — Fri, 19 Jan 2018 14:34:26 GMT

Thank you everyone, this worked perfectly.

Re: SubStringCount

sunny_talwar — Fri, 19 Jan 2018 14:37:11 GMT

Please close the thread if you got what you wanted by marking the correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny