What does the ONLY() function do?

[#1]

From the manual, which clearly wasn't written by a native English speaker:

only(expression )

If expression over a number of records, as defined by a group by clause, contains only one numeric value,

that value is returned. Else, NULL is returned.

Example:

Load Month, only(Price) as OnlyPriceSoldFor from abc.csv group by Month;

First, I've seen many references to the Only() function in my searches here, and I've NEVER seen the 'group by' clause used. Is it necessary? If omitted, is it implied or ignored?

Second, the example shown is from a Load statement, but virtually every use I've seen on this board is in an expression. Are there differences? And, as usual,what is the syntax?

Third, what would that example do? If my table had different sales prices during the month, does that mean "OnlyPriceSoldFor" would be null?

I'm asking these questions because I have an issue with different sales reps being assigned to the same accounts at different times (e.g. Bob has the account, but moves to another division, so then John has the account, but he quits, so now Tammy has the account). Could I use this function to determine if an account has only had one rep? In a simple chart with just the dimension Company, would my expression be:

if(isNull(Only(SalesRep)),'Multiple Reps',SalesRep)   ?

[erichshiino]

Hi,

1) In the example in your post, it is the only way to return the price due to the group by clause.

If you make a ' Load Month, Price from abc.csv group by Month', QV would return an error because Price was used without an aggregation function and it is not in the group by field list as Month is.

I'd say that in a chart, the 'group by' is implied. When you put in a chart the field month, and use only(Price), you are aggregating data in months. In this case, you could just use '=Price' as your expression

2) I'd say that the advantage of only as an expression is that you can use set analysis.

You can write something like this: 'Only (  {<Year = {2012}>} Price ) ' . As I mentioned in answer (1), only(Price) could be written as '=Price' in your expression

3) Yes, it would be null. There is a result if there is only one value

For your problem, instead of ignoring the SalesRep name, you should have a rule to apply the right sales rep according to his assignment to the accounts.

In the community, you can look for 'slowly changing dimension' solutions

Hope it helps (I'm not a native speaker as well... ),

Erich

[Anonymous]

My two cents...

Only() function is an aggregation function, hence the syntax is the same as, for example, for sum(), or max(), or count().  It is for both script and front end.

For the "multiple sales reps per month" problem, it probably makes sense to use concat() function,so you show all reps:

concat(distinct SalesRep, '; ')

Regards,

Michael