DSXchange

Im trying to find out the maximum date by grouping Cus_code (A1,A2,A3..)from the following (.csv file)
using IBM Websphere Datastage(8.0) [parallel Job]

Source Data is like....

A1,2005-06-03,pending,2500
A1,2004-05-04,ok,3400
A1,2004-05-04,pending,4070
A3,2005-06-03,ok,5016
A2,2008-06-03,not ok,6908
A1,2004-05-04,clarified,7854
A1,2005-06-03,reverted,10692
A2,2004-05-04,not ok,11638
A3,2012-06-03,clarified,12584
A1,2030-05-04,pending,13530

The Metadata for the Source is :-
Cus_code Varchar(10)
Update_dt Date
Status Varchar(15)
Amount Decimal

How this can be implemented? whether aggegator can be used?

Please Help me in this regard

Thanks in Advance

Sivaram

Yes you can use a aggregator stage with Cus_code as the group key.

Thnx for ur response!!

I already tried with aggeregator stage as u told, job is aborting with error in director log as

" Aggregator_7: Error when checking operator: When binding input interface field "St_Dt" to field "St_Dt": No default type conversion from type "date" to type "dfloat". [api\interface_rep.C:806]"

NB:My date format is 'yyyy-mm-dd'.

Thanks

Sivaram

hi,

aggregator is only for numeric types.

2 solutions=>

1. transform your date into a numeric value (YYYYMMDD) and use the aggregator

2. sort your data by date in descending order, then use a remove duplicates to get the max of your date

Hi gbusson,

Thanks!! I have tried the first mentioned method ie by converting the date to julian then aggregating and reconverting it to Date. It worked fine...

Is there any other possible ways to convert date to numeric other than julian which I have used here ???


Thanks in advance...

Sivaram

hi,

Stringtodecimal(datetostring(#yourdate#,'%yyyy%mm%dd')

Old topic (I know) but I had to go through this (and other similar ones) since I was looking for a solution.

On a hunch I tried the option Preserve type = True within the Aggregator itself....worked fine... No more warnings / aborts.

Basically what happens I guess is on choosing to preserve the datatype the output of the aggregator remains in iys source format.