Dimensions and other fields

[rdomenoqv]

Hi everybody:

This is the problem:

I have a table that comes in this way:

universal_id    name             amount

123456          John Smith          100

123456          John Smith          350

123456          John Smith          220

789123          Tom Wilson          110

789123          Wilson Tom          400

789123          Tom Wilson          150

789123          Tom Wilson          210

Now, in a straight table, I set 'universal_id' as dimension, and this is the result

universal_id    name             sum(amount)     count(universal_id)

123456          John Smith          670               3

789123          --                  870               4

"Name" comes from different sources, and because of that, when they are different, it can't be "consolidated"

I would like the result would be:

universal_id    name             sum(amount)     count(universal_id)

123456          John Smith          670               3

789123          Tom Wilson          870               4

I tried several ways, but no one went ok

Any suggestions?

Thanks

[Nicole-Smith]

You could make name an expression instead of a dimension and use FirstSortedValue() to only get one of the values:

=FirstSortedValue(name, name)

[antoniotiman]

May be

MaxString(name) or MinString(name) instead of name (expression)

[sunny_talwar]

May be like attached

[rdomenoqv]

Hi Nicole:

I have tested your suggestion; did not work in both forms (added another dimension and/or using FirstSortedValue as you mentioned).

When using FirstSortedValue as expression, result is NULL for all

[rdomenoqv]

Yes! this worked!

Using MaxString(name) instead of name, solved the problem!.

Now, always show a name

Thank you, and everybody in the forum!

[rdomenoqv]

The way you show the ids (in any order, or unsorted) is the natural way in which they come.

I used the example of sorted ids, just to be clear to expose the problem to the forum.

Anyway, thanks to everybody in the forum by your fast answers