Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Wildmatch in Set Analysis on full column

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:

RefStringString1String2String12Count RefString found in String12 colum
abcabcdefabcdef2
defdefghidefghi3
ghighiabcghiabc4
defjkldefjkldef3
mnoghimnoghimno1
ghi-ghi ghi4

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.

7 Replies
Highlighted
Master II
Master II

Re: Wildmatch in Set Analysis on full column

try THIS

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

Learning never stops.
Highlighted
Employee
Employee

Re: Wildmatch in Set Analysis on full column

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

Highlighted
Contributor III
Contributor III

Re: Wildmatch in Set Analysis on full column

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-ghi11
abcabcdefabcdef01
defdefghidefghi01
ghighiabcghiabc01
mnoghimnoghimno01
defjkldefjkldef01
Highlighted
Contributor III
Contributor III

Re: Wildmatch in Set Analysis on full column

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

Highlighted
Employee
Employee

Re: Wildmatch in Set Analysis on full column

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

Highlighted

Re: Wildmatch in Set Analysis on full column

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master II
Master II

Re: Wildmatch in Set Analysis on full column

may be you would like to check this out.

Capture.PNG

Learning never stops.