If condition with wildmatch

[ValeriaBonini]

Hi, I have a table like this:

Product      Category

A                    BP something

B                    BP Analysis

C                    BP test

D                    Something

How can I create a new column "Type" that if category contains BP* it has value 1, if it doesn't contain BP has value 0 and if contains BP test it has value 'other'?

Product      Category                      Type

A                    BP something            1

B                    BP Analysis                 1

C                    BP test                         Other

D                    Something                 0

I tried 

if(wildmatch(Category, 'BP*'), 1,  '0') as Type

but I don't know how to include the last if condition

[Aditya_Chitale · Accepted solution]

Something like this ?

if(wildmatch(Category,'BP test'),'Other',
     if(wildmatch(Category,'BP*') ,'1',
         if(not wildmatch(Category,'BP*'),'0'))) as Type

Regards,

Aditya

[BrunPierre]

As below.

Text(If(WildMatch(Category,'BP*') and WildMatch(Category,'*BP test*')=0,1,
If(WildMatch(Category,'*BP*')=0,0,
If(WildMatch(Category,'*BP test*'),'other')))) as Type