Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
try THIS
count({1<String12 = {"=(wildmatch(String12,'*'& RefString & '*' =1)"}>} RefString)
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."
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 |
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
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))
What is the logic behind to get same output values from your right most column?
may be you would like to check this out.