Hi,
I have a source file with columns productname,saletype,start date. Based on these columns I have to calculate end date for the corresponding records.
If the product is only one record,the end date should be 1-9-9999. Else teh end date is one day previous than the next record's start date.
Below is the example:
Product Name SaleType StartDate EndDate
ABC XYZ 1-2-2009 2-4-2010
DEF MNO 3-4-2010 1-9-9999
ABC XYZ 3-4-2010 1-9-9999
Please suggest me what stage can i use in this scenario to achieve end date calculation.
Thank You.
Calculate New column from exising columns
Moderators: chulett, rschirm, roy
-
hemaarvind1
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
Transformer Stage:
1) Partition by key and sort by key + start date descending
2) Use stage variables to detect key change and retain the previous row's start date for use in the current row's end date derivation
or
Sort Stage + Transformer Stage:
1) Partition by key and sort by key + start date descending
2) Have the sort stage generate a key change column
3) Use stage variable to retain the previous row's start date for use in the current row's end date derivation
In both cases, make sure the sequential file input stage imports your start date as a date because they won't sort properly as a string (you'll probably have to use a variable date format since it appears your leading zeroes for day and month are optional).
Mike
1) Partition by key and sort by key + start date descending
2) Use stage variables to detect key change and retain the previous row's start date for use in the current row's end date derivation
or
Sort Stage + Transformer Stage:
1) Partition by key and sort by key + start date descending
2) Have the sort stage generate a key change column
3) Use stage variable to retain the previous row's start date for use in the current row's end date derivation
In both cases, make sure the sequential file input stage imports your start date as a date because they won't sort properly as a string (you'll probably have to use a variable date format since it appears your leading zeroes for day and month are optional).
Mike