Microstrategy Apply Simple with Certain Text

In Microstrategy I am trying to create a new fact that returns a specific name depending on the type of text contained in the field NAME. Below is what I tried but it did not work.

ApplySimple("case when #0 contains 'As Is' then 'As Is' when contains 'New' then 'New' ELSE 'Other' END",[NAME])

There are a few things to mention here. First I don't think you really want a fact, this looks like an Attribute to me. Facts usually contain something to apply operations like sum/avg/etc. Besides you can count attribute-values or even "translate" an attribute-value to a metric value with f.e. "Max([Attribute])".

I won't say that it is out of the question you really want a fact there, but anything besides min/max/count would not yield a reasonable result. Because what would "Sum('Other')" mean (besides generating an error on your DB)?

That aside, there are two other things in your statement that won't work.

1. The second CASE condition has to repeat the comparison to #0/[NAME], i.e. " when #0 contains 'NEW'". For this you can re-use the positional argument inside your query, no need to pass a duplicated param for that.
2. You don't get to use MSTR functions inside the applysimple query, you have to use DB-specific code. This is one of the downsides of the passthrough functions. This isn't going to be problematic here I think, just something to keep an eye out for when switching DB.

Besides, when you post error-messages and generated SQL code it would be easier to check what you're looking for.

This should work (replace regex w something meaningful, do you want this case insensitive etc etc): ApplySimple("case when #0 ~ 'As Is' then 'As Is' when #0 ~ 'New' then 'New' ELSE 'Other' END",[NAME])