How to use count while analyzing concatenated strings

[Victor_]

Hello. I have a table named CodesList, in which I have only one column, that I called "Code".

I created a TableBox with dimension "Code". In that way, a list of all my codes appeared.

Now I want to create a Measure that does this:
I will analyze a string ConcatenatedCode that I have in a table called MyData. I would like to check every line of MyData and take it's ConcatenatedCode. Then, I will analyze it in the following way:

If the ConcatenatedCode only has codes that are the same as Code, I would like to add one to the count (for that reason, I need to separated my ConcatenatedCode by '; '.

Example: If we are analyzing AA, and the following ConcatenatedCodes:
AA; AA; AA; AA
AA; AA
AA; AA; AB; AA

Then my answer needs to be 2 (because in the first line there's only AA, and in the second line too).

I tried to do this code, but it isn't working:

=Count({<ConcatenatedCode= {"=SubFieldCount(ConcatenatedCode, '; ') = Count({<ConcatenatedCode= {'*$(=Code)*'}>} ConcatenatedCode)"}, Code>} Code)

Does anyone knows how to do it?

[tresesco · Accepted solution]

If understood correct, you can try :

Sum(
	If(SubStringCount(ConcatenatedCode, Code)=SubStringCount(ConcatenatedCode, ';')+1
	,1
	,0
	)
)

[TauseefKhan]

Hi @Victor_,

Check This one:

=Count({<ConcatenatedCode={"=Count({<ConcatenatedCode={'*$(=Code)*'}>} Distinct ConcatenatedCode) = SubFieldCount(ConcatenatedCode, '; ')"}, Code>} DISTINCT Code)


**********Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

[tresesco]

@TauseefKhan @Victor_  Has Qlik introduced a function called subfieldcount()? I couldn't find it.  

[TauseefKhan]

@tresesco 
Qlik does not have a built-in function called subfieldcount(). The correct function to count the number of subfields within a concatenated string in Qlik is SubFieldCount. This function returns the number of subfields within a string based on a specified delimiter.

The SubFieldCount(ConcatenatedCode, '; ') function is used to count the number of subfields separated by the delimiter ';' in the ConcatenatedCode field.