Non numeric values in a measure

[Krobzoo]

Hi. I have a question which I am sure I know the answer is "It can't be done." but I wanted to ask JIC. I have a file that has numeric values in the measure, as well non-numeric values. Obviously Transformer does not like that. Is there a work around?

To help, here is an example: The file looks at the number of births in a particular city. In 2007 there were 5 births, in 2008 there were 0 births, and in 2009 there were less than 3 which is represented in the file as an "*" To complicate things further a blank for a year means that the data was not available for that time period. So in one file I have "blanks" "0s" and "*" as well a typical numeric count. Any suggestions?

[AussiePete2011]

Hi there

Transformer will not be able to handle these values as a measure so I'd recommend getting an ETL process happening that will convert or remove these cells from the data source.

Sorry about that
Cheers
Peter B

[Lynn]

It seems to me the situation where there is no data available could be easily omitted from the cube since you don't really need to say "yes, we have no bananas".

If it is important for users to know how many "less than 3" situations there are then possibly a separate measure that shows a count of those would be useful? It really depends on what your business community is interested in analyzing.

[Krobzoo]

This is what I thought. Unfortunately we do have to say "yes we have no bananas" this year since the year before we may have,  and we may have bananas again next year. ;D

[Lynn]

That's ok...you can transform the no data items to zero for the births measure if need be. You could also create another measure for a count of the no data situations, similar to the under 3 situations.

Just to be extra safe add in some measure about bananas and possibly grapefruit :)

[cognostechie]

Pretty simple. I do this all the time.

What is your data source in Transformer?

If it an IQD or a Query subject, you can easily do this in FM:

Edit the Query item for Years and say

If [year] = '' then '1901' else [year]  -

Also try If [Year] is null then '1901' else '[Year] depending on how the data is. In Tranformer,
in the Time Dimension, edit the property to say beggining year to be AFTER 1901 so it will get excluded. If you want to include it, use another year instead of 1901 and include that.

Similarly for '*', say - If [Births] = '*' then 0 else [Births] . Mark this Query item as a Fact.

That way you will ensure that Transfomer gets only numeric items from that column.

[dtopicMedV]

So, you got:
0 = 0
4, 5, 6... = 4, 5, 6...
* = less than 3
null = n/a
I think that transformer will be able to deal with nulls/blanks so you should be all set there.
I would
1. switch from * to null in your measure field and
2. create add. dimension "More than 0 and less than 3 births" with only one value "Yes" that is used as a filter and additional measure that counts "less than 3 births"
3. you could do same for add. dimension "Data is not available"