Filling a column by text string search in another column

[psh]

Hello:

I am making a simple table and I want to create and fill a new column based on a text string search in a different column.

For example:

If you find "banana" anywhere in the text string in a field in column A, put "banana" in column B

ELSE

If you find "rutabaga" anywhere in the text string in a field in column A, put "rutabaga" in column B

ELSE

put "other" in column B

Unfortunately, I cannot depend on "banana" or "rutabaga" being in the same place in the text strings in column A (or even at all) so I can't just count characters from the beginning or end.

I'm brand new to Qlik Sense (three weeks!) so I hope I have explained myself clearly.

Thanks!

[Taoufiq_Zarra]

Hi,

if I have understood your need correctly

the line you'll need in your script is:

pick(Match( -1,(SubStringCount(A, 'banana')>0),SubStringCount(A, 'rutabaga')>0),'banana','rutabaga') as B,

for example on the example in load script :

Test:

Load pick(Match( -1,(SubStringCount(A, 'banana')>0),SubStringCount(A, 'rutabaga')>0),'banana','rutabaga') as B,* inline [
A
bla blba banana jhjh hjhjh
kljlhkh sdhhq test banana lksdjh
lkdhf fdkjfh gdh rutabaga jkskdj
kljhsd kjshqlkh 
];

the results are:

or

Regards,

[psh]

That's not quite what I want--it's more like I have a table visualization that has a column that looks like this:

asdfbananahjkl

qwerrutabagauiop

bananazxcvbnm

asdfghjklpeach

cilantroqwertyuiop

And I want to create a new column so the table visualization looks like this:

asdfbananahjkl	banana
qwerrutabagauiop	rutabaga
bananazxcvbnm	banana
asdfghjklpeach	other (or none or whatever)
cilantroqwertyuiop	other (or none or whatever)

So I'm looking for something to put in the fx expression editor of the second column that searches for selected strings in the first column and applies a few if/else conditions to populate the second column.

Am I making sense?  Like I said, I'm new to Sense--I hope I'm not asking the question wrong.

Thanks!

[Taoufiq_Zarra]

Welcome to Qlik!
in Qlik everything makes sense, just Qlik 🙂

try to change the script with this new version:

if(wildmatch(A,'*banana*','*rutabaga*')=0,'Other',pick(wildmatch(A,'*banana*','*rutabaga*'),'banana','rutabaga')) as B

the load script will be :

Test:
Load if(wildmatch(A,'*banana*','*rutabaga*')=0,'Other',pick(wildmatch(A,'*banana*','*rutabaga*'),'banana','rutabaga')) as B,* inline [
A
blablbabananajhjhhjhjh
kljlhkhsdhhqtestbananalksdjh
lkdhffdkjfhgdhrutabagajkskdj
kljhsdkjshqlkh 
];

and thus :

Regards,

[psh]

Thanks!

I ended up using just this in the expression editor for the new column::

=pick(wildmatch(A,'*banana*','*rutabaga*'),'banana','rutabaga')

I couldn't get the larger script:

=if(wildmatch(A,'*banana*','*rutabaga*')=0,'Other',pick(wildmatch(A,'*banana*','*rutabaga*'),'banana','rutabaga')) as B

to work - I'm sure I messed up some syntax somewhere.

The shorter version works - it's just places a '-' in fields where neither banana or rutabaga are available, which works for my purposes (for now).  

[Taoufiq_Zarra · Accepted solution]

I just re-tested it, it works in the editor.

Through :

=if(wildmatch(A,'*banana*','*rutabaga*')=0,'Other',pick(wildmatch(A,'*banana*','*rutabaga*'),'banana','rutabaga'))

Regards,

[psh]

That did the trick!

Thanks!