Order the records based on Date Value
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
Order the records based on Date Value
Hi All,
I have a scenario where I need to group the records based on the Order...
Input Records
ITM_INST Start Ts PTY_ID End Ts
123 1/1/2008 456 12/31/2008
123 1/1/2009 456 12/31/2009
123 1/1/2010 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 12/31/9999
In the above Input records I need to get three rows as Output...
I need to group rows 1 to 3 Since its same ITM_INST and PTY_ID...
and then row 4, 5 as as separate rec..
Output Should be
ITM_INST Start Ts PTY_ID End Ts
123 1/1/2008 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 9999
Please let me know how can I implement in Datastage.
Note: I'm using Datastage version 8.5
I have a scenario where I need to group the records based on the Order...
Input Records
ITM_INST Start Ts PTY_ID End Ts
123 1/1/2008 456 12/31/2008
123 1/1/2009 456 12/31/2009
123 1/1/2010 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 12/31/9999
In the above Input records I need to get three rows as Output...
I need to group rows 1 to 3 Since its same ITM_INST and PTY_ID...
and then row 4, 5 as as separate rec..
Output Should be
ITM_INST Start Ts PTY_ID End Ts
123 1/1/2008 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 9999
Please let me know how can I implement in Datastage.
Note: I'm using Datastage version 8.5
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
Thanks Craig...
First I tried with Aggregator Stage using Min(Start date) and max(end date)
But it doesn't return correct values... I have a over lap
Second option using Stage Variable in Transformer by comparing prev row and current row I have rows
0
1
1
0
0
I'm not sure how to to split the records...
First I tried with Aggregator Stage using Min(Start date) and max(end date)
But it doesn't return correct values... I have a over lap
Second option using Stage Variable in Transformer by comparing prev row and current row I have rows
0
1
1
0
0
I'm not sure how to to split the records...
For the sample data you posted, the aggregation approach you mentioned is what I was going to suggest and should work fine... again, for the example you chose to post. Can you show us the input and output data that shows the "overlap"?
Show us your problem child.![Wink :wink:](./images/smilies/icon_wink.gif)
Show us your problem child.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
You're right, didn't notice that the last group was the same as the first group so that aggregation while correct is not what you want.
For your stage variables, 0 means the record is the first record of a new group, the 1 means it is another record in the same/current group. If you have the 8.5+ version, you could use transformer looping to take information from both the 'first' and 'last' record in each group, understanding that they could be the same record.
For your stage variables, 0 means the record is the first record of a new group, the 1 means it is another record in the same/current group. If you have the 8.5+ version, you could use transformer looping to take information from both the 'first' and 'last' record in each group, understanding that they could be the same record.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington