Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mphekin12
Specialist
Specialist

Comparing Two Strings

Hello group.  I'm wondering if there is a way to compare two comma delimited strings to see if any of the substrings in one string match any of the substrings in the other string.

mphekin12_1-1636657092888.png

So in the example above, I would like to create an expression that will return the value 'Existing' if any of the diagnosis codes in the string 'Diagnosis Codes in 2021' are found in the string 'Diagnosis Codes Before 2021'.  This would have to be done within a chart since I'm building out those strings within a chart using Set Analysis.

Thank you!

 

 

2 Replies
MarcoWedel

If you are building those strings in a chart, then you probably have the codes separated already and there might be other methods than comparing the resulting strings.
Can you please post some mock-up data and the relevant expressions of your chart to test solutions with?

thanks

Marco

mphekin12
Specialist
Specialist
Author

Thanks for the reply Marco!  Here is what my data looks like in the underlying table:

mphekin12_0-1636980941215.png

Here is the table that I'm showing the end users after I summarize the data:

mphekin12_1-1636981166852.png

 

I'm trying to figure out if the patient is a 'new' patient based on a year selected by the end user.  To be considered a new patient they either had to not have an encounter in the previous year or have a different diagnosis code in the current year.  As you can see in the data above, this patient should NOT be considered new. 

Here is the formula that I'm currently using (I know it isn't 100% correct due to the string comparison issue that I'm having):

if((count({$<RC.Year={'<$(=max(RC.Year))'}>}DISTINCT PERSON_ID)=0 and count({$<RC.Year={'$(=max(RC.Year))'}>}DISTINCT PERSON_ID)>0)
or
(
(SubStringCount(Concat({$<RC.Year={'<$(=max(RC.Year))'}>}distinct DIAGNOSIS_CODE, ','),Concat({$<RC.Year={'$(=max(RC.Year))'}>}distinct DIAGNOSIS_CODE, ',')))
+
(SubStringCount(Concat({$<RC.Year={'$(=max(RC.Year))'}>}distinct DIAGNOSIS_CODE, ','),Concat({$<RC.Year={'<$(=max(RC.Year))'}>}distinct DIAGNOSIS_CODE, ',')))
=0)
,
'New',
'Not New'
)

 

Any help would be greatly appreciated!