hi,
i have a sequential file stage with input columns :
status varchar
completed_date timestamp
the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.
i used the logic of filtering the columns with status='C' in the 'where' clause and then agreegating. but this did not work since agrregation will not work on time stamp. the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'
need some help on this
finding max date from a file grouping by another column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: finding max date from a file grouping by another column
Hi,Sgiri1 wrote:status varchar
completed_date timestamp
the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.
the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'
You can try this:-
Before passing to aggregator, add one more column for seconds. This will be the difference between your input timestamp and a 'Constant Time stamp'.
* You can use it something like 2009-01-01 00:00:00.000
Then you can use the aggregator to know the min or max timestamp.
the function 'SecondsSinceFromTimestamp'- returns the number of
seconds between two timestamps.
Hi Experts,
If the logic is incorrect, please let me know.