Page 1 of 1
Aggreator Stage - to find max values of a timestamp field
Posted: Tue Aug 01, 2006 4:12 am
by sreelalpp
Please tell me, how to use Aggregator stage to find the maximum value in a time stamp filed.
When I tried using that field for calculation ( max value), it was throwing an error that 'Invalid conversion from timestamp to dfloat'
Is it wheather Aggregator stage does not support calculation( Eg. Max value) on fileds which are Timestamp datatype?
Posted: Tue Aug 01, 2006 4:19 am
by ArndW
Explicitly convert your timestamp into a numeric format using SecondsSinceFromTimestamp and some starting timestamp and then use the aggregator to get the maximum. You can then reconvert this back to a timestamp format after the aggregation.
Posted: Wed Aug 02, 2006 6:51 am
by rwierdsm
This is a little surreal; I just now needed to do the same thing, and got the same error.
Thanks, Arnd, for your idea.
I had only a date, not a date time, so I converted to an integer using:
Code: Select all
DaysSinceFromDate("1900-01-01",From_DB.SNAP_DT)
and went back again using:
Code: Select all
DateFromDaysSince(To_DB.SNAP_DT,"1900-01-01")
Of course, now I have a bunch of other errors to contend with, but this one has gone away
Rob
Posted: Wed Aug 02, 2006 7:30 am
by rwierdsm
I'm starting to come to the realization that I cannot perform any aggregations on non-numeric columns in the aggregator stage
Is this true
I'm trying to do a vertical pivot. Many of my columns are numeric and I can get the surviving value by doing a Max, however, aggregator is choking on all the character columns that I apply a Max to.
Rob
Posted: Wed Aug 02, 2006 12:35 pm
by Ultramundane
I made Ascential give me a patch for this garbage. Get the patch and you can specify preserve_type as true and change the output datatype as necessary for the output. The patch allows you to get MAX and MIN of character, date, timestamp columns.
Ecase is 61040.
Posted: Wed Aug 02, 2006 12:58 pm
by chulett
Odd that you would have to get a patch to enable something like that. Seems like pretty basic funtionality to me...
Posted: Wed Aug 02, 2006 1:15 pm
by Ultramundane
Because I had it fixed about 1 year ago I would not be surprised if it has been added as basic functionality to 7.5.2.