topic Re: Wildmatch in Set Analysis on full column in QlikView

Wildmatch in Set Analysis on full column

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am trying to count the times a certain field value is found in a different column with multiple strings. Resulting in a table like this:

RefString	String1	String2	String12	Count RefString found in String12 colum
abc	abc	def	abcdef	2
def	def	ghi	defghi	3
ghi	ghi	abc	ghiabc	4
def	jkl	def	jkldef	3
mno	ghi	mno	ghimno	1
ghi	-	ghi	ghi	4

I have RefString, String1 and String2 as values from a database. My main issue is to find a formula for the rightmost column.

I have tried the following:

count({1<String12 = {'*$(=RefString)*'}>} RefString)

An excel formula that does this is as follows (Take Top left as A1):

=COUNTIF(D:D,"*"&A2&"*")

Thanks for your help in advance.

Re: Wildmatch in Set Analysis on full column

pradosh_thakur — Wed, 03 Jan 2018 12:16:14 GMT

try THIS

count({1<String12 = {"=(wildmatch(String12,'*'& RefString & '*' =1)"}>} RefString)

Re: Wildmatch in Set Analysis on full column

Clever_Anjos — Wed, 03 Jan 2018 12:21:17 GMT

Please note that Set Analysis is evaluated before the graph expansion.

So you can´t build a SA excpression that is evaluated with different values for each row

Please refer to this great article by hic‌ A Primer on Set Analysis

"The condition, however, is in itself like a selection that is evaluated before the cube (the chart) is expanded. Hence, it is not possible to have a condition that is evaluated row by row."

Re: Wildmatch in Set Analysis on full column

Anonymous — Wed, 03 Jan 2018 12:28:29 GMT

No luck with that formula:

RefString	String1	String2	String12	*=count({1<String12 = {'-'}>} RefString)*	*count({1<String12 = {"=(wildmatch(String12,''& RefString & '') =1)"}>} RefString)*
				1	6
ghi	-	ghi	-ghi	1	1
abc	abc	def	abcdef	0	1
def	def	ghi	defghi	0	1
ghi	ghi	abc	ghiabc	0	1
mno	ghi	mno	ghimno	0	1
def	jkl	def	jkldef	0	1

Re: Wildmatch in Set Analysis on full column

Anonymous — Wed, 03 Jan 2018 12:29:43 GMT

So if I create String12 during the data load would this then be possible?

That way String12 is not created during the evaluation but can already be used in the SA

Re: Wildmatch in Set Analysis on full column

Clever_Anjos — Wed, 03 Jan 2018 12:34:12 GMT

The 'problem' (it´s not a problem, it´s the way SA works) is that you´re trying to use RefString (it changes for each line)

count({1<String12 = {'*$(=RefString)*'}>} RefString)

Try using a regular if, as it is evaluated for each row

Sum(If ( WildMatch(String12, '*'&RefString&'*',1,0))

Re: Wildmatch in Set Analysis on full column

Anil_Babu_Samineni — Wed, 03 Jan 2018 12:39:58 GMT

What is the logic behind to get same output values from your right most column?

Re: Wildmatch in Set Analysis on full column

pradosh_thakur — Wed, 03 Jan 2018 12:40:09 GMT

may be you would like to check this out.