Remove values from a dimension

[PrashantRupani]

Hi,

I have a table object on the sheet with Country column.

The expression for that is-     =if(len(Country)=0,'NULL' , Country)

I would like to make a change to this expression. Remove a specific country for example Italy. Also need to keep the existing expression intact. 

How do I do this?

Thank you. 

[Vegar]

Try this :

  =if(match(Country, 'Italy', 'Sweden' ) or (len(Country)=0) ,'NULL' , Country)

[PrashantRupani]

That did not work. 

For example the values in Country are -

USA 

Germany

Italy

-

Then I need to show  this below ( Remove Italy and replace '-' by NULL)

USA 

Germany 

NULL

[Vegar]

Consider this data model.

Data:
LOAD * inline [
Country, Amount
Sweden, 10
USA, 12
Italy, 14
Germany, 18
, 30
];

A chart with this expression: =Sum(Amount)

And this dimension:=IF(match(Country, 'Italy') OR len(Country)=0, 'NULL', Country)

Then you can get something like this:

[PrashantRupani]

I see the way you are doing this.  I tried this solution. 

But I need NULL to show the bar value of 30 . not 40. 

Do not want to add the two. Need to remove Sweden and still show bar value of 30 for NULL.

[PrashantRupani]

I think I found the solution.

First I need to do this in load script - 

if (not Match(COUNTRY,'Italy'), COUNTRY ) AS V_COUNTRY,

Then in the column expression in the table object: 

=if(len(trim(V_COUNTRY))=0,'NULL' , V_COUNTRY)

Please let me know if this is an ok solution. Would like to hear from you . You're the expert. I am the newbie.

[Vegar]

That should work, but if you are to make changes in the script then why don't you fix the empty values at the same time?

if (Match(COUNTRY,'Italy') or (len(trim(V_COUNTRY))=0), 'NULL',COUNTRY ) AS V_COUNTRY,

Then you don't need to use a calculated dimension in your application, just use V_COUNTRY

[Vegar]

Ok I missed your previous reply when answering yesterday. Lets do another approach.

Create the NULL values in the script and exclude your countries in the expression using set analysis. 

DATA:
LOAD
  if (len(Country) >0, Country, 'NULL' ) AS V_COUNTRY,
  *
inline [
  Country, Amount
  Sweden,  10
  USA,     12
  Italy,   14
  Germany, 18
         , 30
];

Expression in chart:

=sum({<V_COUNTRY-={'Italy'}>}Amount)

[PrashantRupani]

The Load script worked fine.

However I am not using the chart. I am using a table object. In the table, I am adding Country as dimension and need to exclude Sweden. How do I do that?

Thanks.