Using Where clause in Expression Editor

[Former Member]

I 'm trying to build a calculated column in Attribute view. In expression editor I'm trying to build a formula like "CITY" where "CITY" like 'ABCD'.

I get an Invalid expression warning.Can somebody let me know how to use Where clause inside the expression editor.In general where can I find the expression editor functions supported apart from the ones listed there?

[former_member184768 · Accepted solution]

Hi Sridhar,

First thing, what exactly do you mean by expression editor functions supported "apart" from ones listed there. Mostly only those functions are supported which are mentioned in the expression editor. You can find the details in the help (Press F1 and search for "Functions used in Expressions".

Secondly if you wish to use the WHERE condition, then you may have to use IF function, something like

if("CITY" = 'ABCD', "CITY", '')

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Thanks for your reply.

It is a nice work around to use an if function to achieve what we can using the where condition. But Can't we use a where condition to filter the values here?

regards,

Sridhar

[former_member184768 · Accepted solution]

Hi Sridhar,

The expression editor used in Calculation column definition provides limited functionality. The WHERE condition for a column is also applicable in case of Restricted Column, but that is different from the Calculated Column.

I think, as per the documentation, using the IF condition is the only workaround available.

Can you please mention your exact requirement and what do you have in mind so that we can look into alternatives (even in the front end reporting tool) for your requirement.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Thanks for your time. My requirement is just to create a calculated column as below:

calculated column = ("CITY" WHERE "REGION"='APAC') so that my calculated column only returns the filtered values.

regards,

Sridhar

[former_member184768 · Accepted solution]

Hi Sridhar,

What value do you expect where the REGION <> 'APAC' ? If you need to restrict all the rows with Region = 'APAC' then you might want to use a projection.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Thanks for your reply. I do not want to filter on the table with  Region = 'APAC'. I just need to create a calculated column which will return filtered values based on the condition. I have achieved it for now using If condition as you suggested as a workaround.

regards,

sridhar

[Former Member]

If my understanding is correct that you are looking to filter with similar functionality as where in a SQL Statement, I believe you can use constraint filter, you can use filters either on normal columns or on calculated columns, right click on a column ( or calculated column) for context menu where you can find this option.

[Former Member]

Hi Abhishek,

Thanks for your suggestion.I do not want to filter on the column in that way as it will restrict the values completely.Instead I just want to create a new field where I can restrict the values based on the conditions using where clause.

regards,

Sridhar