Wildmatch in Set Analysis on full column

[Anonymous]

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.

[pradosh_thakur]

try THIS

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

[Clever_Anjos]

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."

[Anonymous]

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

[Anonymous]

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

[Clever_Anjos]

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))

[Anil_Babu_Samineni]

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

[pradosh_thakur]

may be you would like to check this out.