Aggregation function with data type String

[former_member222671]

Hi all,

do you know if there is a restriction concerning aggregation functions coming from a .unx for a measure with the data type String?

One of our clients tried to use the aggregation function "max" for the data type "String" from a universe in Lumira.

But trying to create a data set with this measure, the following message appears:

"Column "XYZ" has invalid aggregation function "max" for data type "String"."

She also tried to recreate the measure using Lumira's built-in aggregation function in combination with String, but it didn't work either.

In the user guide, I only found these limitations, so I hoped everything else would work:

When the column data type is Numeric, any aggregate function can be used for the measure.

When the column data type is Date or String, neither Sum nor Average can be used.

We are currently using Lumira 1.31.

Thanks and BR

Agnetha

[varunanand · Accepted solution]

Does the data in column "XYZ" have both character and numerical values ?

[former_member222671 · Accepted solution]

Hey Varun,

the column contains values in the following formats:

56.4%

34.4

Therefore we couldn't make the measure's data type numeric.

BR

Agnetha

[varunanand · Accepted solution]

Correct me if I'm wrong in the assumption. So you have few rows with the % and few of them just with the number.

You can remove the % using the Data Actions like Trim after <space> or Replace with <blank>

Then you will have only the numeric value which should enable all aggregation types when converted to a measure.

[former_member222671 · Accepted solution]

Yes, this would be a possible solution / workaround.

But I'm still wondering if there is a known limitation stating that Strings can not be used in combination with the "max" function.

Do you know anything about this by accident?

[varunanand · Accepted solution]

In Lumira, dimensions can be Characters,Numeric,Geo,Date or Time fields. By default anything which has combination of numbers and text is considered character, like in your case as there is number and %.

Only for numeric type all forms of aggregation are supported. Others when converted to measure only take Count (distinct/all). So the only way to achieve the aggregation you desire, is to change to numeric.

[TammyPowlas]

Can you make it a measure back in the Universe?  Then it would work

[former_member222671]

Hey Tammy,

it is already a measure back in the universe.

Creating a dataset in Lumira using this measure does not work and the error message above is displayed.

BR

Agnetha