Showing exactly one of multiple possible values in table object

[#1]

Hi,

in a table object I want to show exactly one of multiple possible values.

The dimension is KN_ID.

One KN_ID can have multiple OB_ID and therefore multiple values for OB_Type. In the Table I want to show OB_Type as a measure but I do not want to do any aggregation. I just want the column OB_Type to show one of the possible values for OB_Type for KN_ID.

So far I tried achieving that by using concat and other string functions, but I could not find a satisfying solution.

Could somebody help me with that?

Example is attached

Best regards and thanks in advance

thorben

[OmarBenSalem]

Hi Thorben,

The trick is to use Aggr( Min( KN_ID),OB_ID) instead of KN_ID as a dimension.

What does Aggr( Min( KN_ID),OB_ID) is taking the Min (so only one) KN_ID by OB_ID

See the attached app:

[Anonymous]

Try using Maxstring(OB_Type ) as expression.

[#4]

okay, so maxstring does pick the last value (alphabetically ordered) of OB_Type?

This solution would be okay... But it would be great if the value selection would be more random.

Is there really no function to just randomly pick one of the values?

[#5]

thank you. I appreciate your response.

Unfourtunately, your solution does not work if you do not choose OB_ID as a dimension.

As the dimension is already complex enough in the real application, I would prefer not to change it.

[Anonymous]

Am not sure what is the purpose of selecting a random value from the category?

[#7]

For example:

In the real application I have a field OB_Date. It is like OB_Type, so one KN_ID can have multiple values for OB_Date. If I use MaxString here it will always return the date which is nearest in the past. Such implications should not be made in the table, it should just show one value not always the biggest oder the smallest.

However if that is not possible I guess I will have to go with maxstring

[Anonymous]

I wonder if there is any simple solution for your requirement. Lets see if some expert can help you out.

[karthiksrqv]

Hi Thorben,

This may work:

=subfield(MaxString(rand()&'|'&OB_Type),'|',2)

[#10]

I would have guessed it works but there are always some empty values in the resulting column...

Maybe if OB_Type is empty rand()&'|'&OB_Type is just the random number plus |. And then subfield returns an empty String.

I guess if OB_Type never was an empty string but null it should work. I am going to test it.