creating a new field based on values of existing field

[hopkinsc]

Hi all,

i can't get something to work and was wondering if anyone could help.

basically if i have a field called 'BI' and it holds values like..

QlikView

Qlik Sense

MIS

Qlik Sense, MIS, QlikView?

Business Objects

Business Objects, (QlikView), Qlik Sense

(QlikView), Qlik Sense?

i want to create a new field based on the above values, basically if any rows have either Qlik or Qlik Sense or QlikView then i want 'Yes'  otherwise 'TBC' as the new field. but thsi is what i can't get to work, if any rows have QlikView or Qlik Sense are in brackets or has a ? after it then i want this to show as 'TBC' too.

i can get it working partially but i am having problems with the rows that have something like ...

Qlik Sense, MIS, QlikView?

this row is appearing as TBC where i want it to show 'Yes' as it has been confirmed that Qlik Sense is being used.

Does that make sense?

so for the above examples it would be

BI                                                                            QlikUser

QlikView                                                                  Yes

Qlik Sense                                                               Yes

MIS                                                                         TBC

Qlik Sense, MIS, QlikView?                                      Yes

Business Objects                                                     TBC

Business Objects, (QlikView), Qlik Sense                  Yes

(QlikView), Qlik Sense?                                            TBC

this is what i am currently using..

if(

  index(lower(BI),'qlik')>0 and

  index(lower(BI),'(qlikview)')=0 and

  index(lower(BI),'?')=0,

  'Yes',

  'TBC'

  ) as QlikUser,

any help would be appreciated.

[ashfaq_haseeb]

Hi,

Try like this

Load * ,if(WildMatch(BIQlik,'*Qlik*'),'Yes','TBC') as QlikUser;
load * Inline
[
BIQlik
QlikView
Qlik Sense
MIS
Qlik Sense, MIS, QlikView?
Business Objects
Business Objects, (QlikView), Qlik Sense
(QlikView), Qlik Sense?
]

Regards

ASHFAQ

[tresesco]

... or has a ? after it then i want this to show as 'TBC' too.

This logic is making it appear as TBC. I guess you have to refine your logic for classification.

[Chanty4u]

xhk dis

[jonathandienst · Accepted solution]

How about:

    If(WildMatch(Replace(

            Replace(

                Replace(

                    Replace(Lower(BI),

                        'qlik sense?', '*'),

                    'qlikview?', '*'),

                '(qlik sense)', '*'),

            '(qlikview)', '*'

            ), '*qlik*'

        ), 'Yes', 'TBC'

    ) As QlikUser