Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

dgaravaglia
New Contributor II

SubStringCount

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

Tags (1)
8 Replies
Highlighted
Luminary
Luminary

Re: SubStringCount

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

Highlighted
dgaravaglia
New Contributor II

Re: SubStringCount

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. 

Highlighted
Luminary
Luminary

Re: SubStringCount

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

Highlighted
dgaravaglia
New Contributor II

Re: SubStringCount

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

Highlighted
Luminary
Luminary

Re: SubStringCount

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?

Highlighted

Re: SubStringCount

Try this

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

Highlighted
dgaravaglia
New Contributor II

Re: SubStringCount

Thank you everyone, this worked perfectly. 

Highlighted

Re: SubStringCount

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