SubStringCount

[Anonymous]

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

[juraj_misina]

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

[Anonymous]

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. 

[juraj_misina]

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|')

[Anonymous]

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

[juraj_misina]

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?

[sunny_talwar]

Try this

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

[Anonymous]

Thank you everyone, this worked perfectly. 

[sunny_talwar]

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