topic Re: String functions for "find several values" in QlikView

String functions for "find several values"

maxime_gerard — Mon, 20 Feb 2017 11:08:24 GMT

Hello,

I'm looking for a way to define if multiple substrings are contained in a string. Actually I'm talking about months.

I want for example to know if one of the months of the string :

'Jun,May'

is contained in the string :

'Apr,Aug,Dec,Feb,Jan,Jul,Jun,Mar,May,Nov,Oct,Sep'

The functions like substringcount of index will not work (alone at leaste) because it's actually 2 differents substring that I'm looking for.

The strings will dynamically change with selections.

Any ideas?

Thanks!

Re: String functions for "find several values"

petter — Mon, 20 Feb 2017 15:56:16 GMT

How many individual months can be in both of the strings are we taking about a handful or just a unique set of maximum twelve?

Re: String functions for "find several values"

petter — Mon, 20 Feb 2017 16:08:47 GMT

It might look a bit horrible this expression but it seems to do the trick...

=Alt(SubStringCount(v2,SubField(v1,',',1)),0)
+Alt(SubStringCount(v2,SubField(v1,',',2)),0)
+Alt(SubStringCount(v2,SubField(v1,',',3)),0)
+Alt(SubStringCount(v2,SubField(v1,',',4)),0)
+Alt(SubStringCount(v2,SubField(v1,',',5)),0)
+Alt(SubStringCount(v2,SubField(v1,',',6)),0)
+Alt(SubStringCount(v2,SubField(v1,',',7)),0)
+Alt(SubStringCount(v2,SubField(v1,',',8)),0)
+Alt(SubStringCount(v2,SubField(v1,',',9)),0)
+Alt(SubStringCount(v2,SubField(v1,',',10)),0)
+Alt(SubStringCount(v2,SubField(v1,',',11)),0)
+Alt(SubStringCount(v2,SubField(v1,',',12)),0)

And as long as v1 contains a maximum of 12 values the v2 could contain as many more values.

v1 and v2 could be either variables (with strings) or one specific field value in a row in a chart.