Doing Maximum of character column in an Aggregator

[splayer]

I am using Parallel Extender. In my aggregator, I have a Column for Calculation. In the Maximum Value Output Column, I have 13 character values. I have defined this column as a character value throughout but it contains numbers only. I would like the aggregator to do maximum on it. So I have values like this:

1234567890123
2222222222222
3333333333333

I would like the aggragtor to return: 3333333333333. However, it returns something like: 3.3333e+12. That is, it converts the string into a number. Can the aggregator do a maximum on string values? Or number values as a string.

Thanks.

[estevesm]

I am using Parallel Extender. In my aggregator, I have a Column for Calculation. In the Maximum Value Output Column, I have 13 character values. I have defined this column as a character value throughout but it contains numbers only. I would like the aggregator to do maximum on it. So I have values like this:

1234567890123
2222222222222
3333333333333

I would like the aggragtor to return: 3333333333333. However, it returns something like: 3.3333e+12. That is, it converts the string into a number. Can the aggregator do a maximum on string values? Or number values as a string.

Thanks.

I'm also interested in the solution of this problem, as I also want to do max on char columns and all I get is warnings saying my data will be converted to float. Can't the aggregator stage do max/min on char columns?

[ray.wurlod]

That's a question that rightly should be directed at the vendor (Ascential Software). If it can not form aggregate functions properly on character data, even though the data contain only numeric characters, this is a bug.

[estevesm]

Just to let you guys know that I spoke to Ascential support and they told me this is a bug that will (probably) be fixed in the next release (7.6).
In summary, if you use the aggregator stage to do a MAX on a character column, the value gets converted to dfloat and back to string yielding an incorrect result. If you try the same with a date column the job aborts, saying it cannot convert a date to a dfloat.
Does anyone know a workaround to do SQL-like aggregation on PX?

Regards
Marcelo

[tonystark622]

You could add a dummy column that contains a character and the data from the field you're aggregating and do a max on your dummy field. It won't convert it to a number if it has a character in it, right?

Ex: Your data is: 12345678

Add a char to it and put it in a new field ---> 12345678Z

Do the max on the new field and strip the last char off.

Tony

[Ananda]

I faced the same issue. I was grouping on Column X,Y and Z and taking maximum of Column A which is CHAR(9). Data loaded in target was in exponential format something like this 12312e5+.

After figuring out that in DS v8.1 Aggregator stage does not perform agg functions on varchar columns, I then added Column A to grouping of the columns and kept it as CHAR(9) in output column page. It worked.

[soumya5891]

First convert the character field into Integer in transformer stage variable ,then aggregate,and again bring back to char again by using a transformer