Hi,

I have encountered an issue with a created column calculation in Apptio Studio.  I am trying to create an IF statement using an IN function and wildcards simultaneously.  Is the wildcard supported in this type of function?  Here is the formula I'm trying to use:

=If({Accounting Event Description} IN "*- A-*", "Works", "DNW")

Any help you can provide is appreciated.  Thanks.

Best Regards,

Andre Largie

Hi Andre,

one possibility should be the function Search or Find:

=If(Search({Accounting Event Description},"- A-")>0, "Works", "DNW")

Find is the same as Search, except that Find is  case-sensitive.

Regards

Holger

If you're only interested in returning one of two values, leveraging the Search function in the If function would be an excellent way to go.

If you envision other criteria being analyzed and/or return values, I recommend taking a look at the TableMatch function in Help documentation.

I think the IN function requires parenthesis.  So your syntax would be:

=If({Accounting Event Description} IN ("*- A-*"), "Works", "DNW")

That said, I don't know if the IN function support wildcards.  Your best bet, if you're only looking to find one set of characters, is to go with Holger's suggestion and use SEARCH, but I think the syntax in Holger's example is backwards.  It should be:

=If(Search("- A-",{Accounting Event Description})>0, "Works", "DNW")

Thanks for the insight.  The search function worked great and I've been using it ever since.  It's a bit taxing on the calculations but works like a charm.