Wildmatch with a COLUMN/ Variable

[#1]

Hi,

Below is the data I'm working on and the Results column is what I want to obtain.

Basically, I want to check if [PROD SKILLS] is inside the [SKILLS SET] then Results = 1 otherwise 0.

PROD SKILLS	SKILLS SET	Results
apple	banana apple carrots	1
carrots	carrots	1
banana	carrots apple	0

Appreciate your help on this. Thank you!

[varshavig12]

if(substringcount([SKILLS SET],[PROD SKILLS])>0,1,0)

[Anil_Babu_Samineni]

Varsha Solution might work but you cab try this too

if(substringcount([SKILLS SET],-[PROD SKILLS])>0,1,0)

[sunny_talwar]

Or even with Index

If(Index([SKILLS SET],[PROD SKILLS])>0,1,0)

Sample Script:

Table:

LOAD *,

  If(SubStringCount([SKILLS SET],[PROD SKILLS]) > 0, 1, 0) as Varsha_Exp,

  If(Index([SKILLS SET],[PROD SKILLS]) > 0, 1, 0) as Sunny_Exp;

LOAD * INLINE [

    PROD SKILLS, SKILLS SET

    apple, banana apple carrots

    carrots, carrots

    banana, carrots apple

];

[Anil_Babu_Samineni]

Sunny, Index and Subfieldcount two function working as Same or what?

[sunny_talwar]

They work similarly but not the exact same.

[varshavig12]

Doing the same thing with index is new for me. Thanks!

[swuehl · Accepted solution]

Since you were explicitely asking for a Wildmatch expression:

If(WildMatch([SKILLS SET],'*'&[PROD SKILLS]&'*'), 1,0) as WildMatch_Exp

PROD SKILLS	SKILLS SET	Varsha_Exp	Sunny_Exp	WildMatch_Exp
apple	banana apple carrots	1	1	1
banana	carrots apple	0	0	0
carrots	carrots	1	1	1

[varshavig12]

If(WildMatch([SKILLS SET],'*'&[PROD SKILLS]&'*'), 1,0) as Stefan_Exp