topic Re: Comparing Two Strings in QlikView

Comparing Two Strings

mphekin12 — Thu, 11 Nov 2021 19:04:29 GMT

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.

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!

Re: Comparing Two Strings

MarcoWedel — Sat, 13 Nov 2021 21:42:02 GMT

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

Re: Comparing Two Strings

mphekin12 — Mon, 15 Nov 2021 13:01:12 GMT

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

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

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!