Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this :
=if(match(Country, 'Italy', 'Sweden' ) or (len(Country)=0) ,'NULL' , Country)
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
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:
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.
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.
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
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)
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.